Copy Current (Selected) Sheet To New Workbook And Save As Tab Name
Feb 28, 2014
I want to take the current sheet and copy to a new workbook (keeping the tab name) and then save as the tab name (xlsl) and close.
I've found ones that do a Saveas box or ends up saving as "Sheet1," "Sheet2" etc but can't seem to find one that keeps tab name and saves.
Here is the one I was using, but it saves all tabs not just selected:
Code:
Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer
[Code].....
View 1 Replies
ADVERTISEMENT
Jul 5, 2009
I have an excel file which I use when I book keep journals in work. Each time I come across a new piece of bookkeeping I add to the file, name the sheet and index it. (I've attached a loose example for illustration) so the bookkeeping template.xls gets bigger every day.
Currently, when I book keep on a particular day, I open the template.xls workbook;
draft my journals;
and then manually copy the worksheets I have completed during the day from the template.xls workbook and paste the sheets into a new book and save the days bookkeeping as the current date.
With the file I have attached can someone show me how to write a macro and apply it to each of the buttons on the various work sheet tabs (each button will run the same macro).
Upon clicking a button, I would like the macro to;
Copy the active sheet into a new book and save the file as the current date.
Each subsequent click of a button the macro will check if the .xls file exists for the current date, if it does, then it will just copy the active worksheet to the file that has already been created.
In the new file which is saved as the current date, I need to remove the macro button and the hyperlink that reads back to index.
I have had some helpful macro's provided from another forum, however, the other forum does not appear to enable users to upload files, so I can't quite convey what it is I am trying to achieve.
View 14 Replies
View Related
May 4, 2006
I get an xl file sent to me every day which I upload into a database. I then save the file keeping its name and save it into a selected folder. Is it possible to add a macro at the beginning of my current macro that saves the file using its name to a selected folder?
View 4 Replies
View Related
Jun 17, 2014
I have been trying to edit a code which previously saved a copy in a new workbook to a specific folder/path. (Additionally it copies and clears some figures, but this is working as it should.)
However I would like the copy to be saved at the same location as the original workbook, regardless of the path the original workbook is saved.
I.e if I need to move the workbook containing the code to a new folder/location, when using the macro, the new copy should be stored in the same folder/location as the original one.
For now it is only saving the copy into "My Documents"
Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 16-02-2009 by ceng
'
Sheets("Bunker ROB").Select
Sheets("Bunker ROB").Copy
ActiveWorkbook.SaveAs Filename:= _
ActiveWorkbook.Path & Range("D3"), _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
[Code] ........
View 2 Replies
View Related
Feb 2, 2014
I have the below code that saves selected sheets of my workbook as pdfs in the current file location. What I would like this code to be able to do is to create a new folder (named with todays date), and then save each of the pdfs into this folder.
Code:
Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet
[Code] ........
View 3 Replies
View Related
Oct 27, 2008
EXAMPLE: Complete Sheet called "Day1". When day1 is complete you click on button and it then copies itself and creates and renames new sheet to "Day2", then when "Day2" is complete you click on button and it then copies itself and creates and renames new sheet to "Day3", and so on and so forth to "Day30".
View 9 Replies
View Related
Sep 11, 2007
I have a workbook that has worksheet called "Data". In that workbook, there is some codes for the event of "workbook_beforeprint". My question is whether i could create a copy of the "master" workbook and delete all the unwanted sheets but "Data" sheet using VBA. There are some codes of creating a new workbook but i suppose i need to the exact copy of the current workbook using VB
View 3 Replies
View Related
Apr 9, 2014
have a basic knowledge of VBA. The task I am trying to perform with VBA is to
1. Copy from sheet1 A1 to sheet2 E9
2. Copy from sheet1 B1 to sheet2 E11
3. Save sheet2 in a new workbook file with the file name cell data from sheet1 A1 (511894.xls)
4. Copy from sheet1 A2 to sheet2 E9
5. Copy from sheet1 B2 to sheet2 E11
6. Save sheet2 in a new workbook file with the file name cell data from sheet1 A2 (097219.xls)
7. Repeat the process down columns A and B to the end of the columns.
8. Columns A and B will end at the same time but the data and the last cell will change with each scan added to the columns.
This code will accomplish this task with a single column of data but not two columns. Also column B will have a mixture of numbers and letters in its cells, Column A is only numbers.
VB:
Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name
[Code].....
Here are some images of the data sheet and the saved sheet
Sheet1.jpg Sheet2.jpg Note: sheet2 in image is "temp" sheet in code
View 7 Replies
View Related
Aug 27, 2012
I need VBA code for the following - I have a worksheet with seven colums of data (A to G) - I need to copy the first column (A) from the active worksheet then open master workbook called 'master' and paste the data in to column D - then save the 'master' as the name in cell Z1 of the 'master' workbook. Once this has been been completed I need to repeat the process but this time copying column (B) and so on.
View 4 Replies
View Related
May 3, 2007
I have a sheet that users fill out and there is a macro on that sheet to copy the info to another workbook, save and close.
for some reason, the date, which originates from a cell with the value "=Today()", and when pasted in to the new book, it is pasted values.
the issue is, every so often, the date will say 09/26/10 and continue to do so for quite a few entries.
i have tried almost everything. I checked the dates on the computers, and all are fine. i even went through it step by step, and when the data pasted it, it was 09/26/10.
View 9 Replies
View Related
Sep 15, 2014
Code to copy the sheet that get selected by if-then-elseif statement and paste in a new workbook, save the workbook as temp, email it and delete it,
sub sending_mail
Dim OutApp As Object
Dim OutMail As Object
Dim rng As Range
Dim DataWB As Worksheet
[Code] ......
View 2 Replies
View Related
Jun 22, 2007
I'm trying to save a file with current date and time...I used the code from the following thread:
Save File Name With Date/time Stamp
ActiveWorkbook.SaveAs Filename:="C:FinalOutput.xls " & _
format(Now(), "mm_dd_yyyy hh mm AMPM"), FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
Now when I try to run the macro, it gives me an error saying: Compile Error, Wrong number of arguments or invalid property assignment. And it highlights "format" in yellow.
View 9 Replies
View Related
Dec 4, 2007
I recorded a macro to save a workbook to a specific folder using the current file name. However, I've tried to remove the absolute reference to a specific filename but it is not working for me. Am I close?
ActiveWorkbook.SaveAs Filename:= _
"H:Files and DocumentsPROJECTSSR 2 SS Phase Two Component ReliabilityWinTrac Files xt_conversionsexcel_version" & SheetName _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
View 2 Replies
View Related
Jun 2, 2009
I have a form button I am currently using to save the workbook. However due to the workbook being so large is taking a little longer than I want to save. Is there a way to change the macro to save the current sheet only?
Sub Save()
'
'
'
ActiveWorkbook.Save
End Sub
View 9 Replies
View Related
May 30, 2012
I've been searching for code that will perform the following:
1. Save the current workbook then,
2. Save the current sheet as a tab delimited file to the desktop with the same name as the current workbook then,
3. Return to .xlsx file format
The file saved to the desktop can be overwritten each time the macro is run.
View 4 Replies
View Related
Aug 21, 2014
I have an open workbook (A) and this is where the code should reside. I want to use VBA to copy the content of an entire worksheet from a closed workbook (B) to an existing worksheet in workbook A. How would you accomplish this?
View 7 Replies
View Related
Mar 11, 2014
I have 2 workbooks. 1 is where data is entered called wksPB, the other is like a database workbook. The wksPB has data to be feed from column B to F and there is a dropdown.
what im looking to do is, if the combobox value is selected as Decline then it should display a messagebox that reference value in cell B is denied. if combobox value is Agreed then the macro should search the database workbook for the text entered in column b of wksPB and then copy data of C:E of wksPB to H:J of database sheet where that text is found and also the approver name in wksPB C24 to the approver cell of where that text was found. I've reached till finding the text but what I get my head twiting on is how to copy the text from wksPB column B:F to database sheet column H:K. Im attaching the sample workbooks and the code where ive reached till.
[Code] .....
Attached Files
Replacement Records - 2014.xlsx
forum file.xlsm
View 1 Replies
View Related
Mar 2, 2014
I'll soon have an ETL process that will load about 150K rows into an Excel workbook. On of the columns will be the end user's userid.
I need to autofilter that external workbook based on the end user's userid, copy that range, clear a worksheet on the current workbook, and copy that range to the current worksheet.
For example, see the attached workbooks. I need to replace the data in Source.xlsb!Cases with the data in Output.xlsb!Case_List, filtered on my userid, which we'll call foo.
View 1 Replies
View Related
Mar 4, 2010
I also use VBA in a terminal emulation program that I use the following command to 'screen scrape' the current display memory and quickly Save it to a .txt file:
.SaveDisplayMemory "C:File1.txt", rcOverwrite
What I want to do in Excel is, Copy the current selected Range() and Save it to a .txt file.
I know how Save the current Sheet to a .txt file, but can't figure out how to Save just a Range() of cells.
View 9 Replies
View Related
Oct 30, 2006
I have developed a macro, run by a button click, in my workbook.
I have about 5 other workbooks to process, what I want to do is to have my workbook with my macro open, then either copy or import these other 5 workbooks, one at a time, into my current workbook so I can run my macro on their data. Not sure if I need to copy them in or not, but thats all I can think of. I'm not even sure how to do this manually, I tried having my workbook open and doing a file - import on the .xls file, but it seems to be translating my data and I'm ending up with a value of 'f1' in one of my columns, no idea what that is. All I really want to accomplish is to run my macro on each of the 5 workbooks, one at a time.
View 4 Replies
View Related
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 Replies
View Related
Mar 12, 2014
I have a workbook with Macros in it, now I want to create a copy of that workbook using VBA.
Here are the steps i want:
1. Create a copy of an open workbook
2. Open the Copy.
3. Close the original workbook.
View 1 Replies
View Related
Mar 27, 2008
i have a workbook that has a lot of sheets but i need to pull information from the one sheet "Veneer Log" i Need it to make new sheets with the same heading as on the "Veneer Log" (Rows 1 & 2) Sheet but it needs to be filtered by the "Product" Column (H) with a new sheet made for all the diffrent products i.e. Dimensional, Drywall, Corners - Thin V., Accents,..... so each product will have a new sheet with i am hoping someone can help me with this. This log changes Daily and it would be nice to have a sheet with only the same product on it to compare new orders so we can batch run. i hope i have given you enough information so someone can help me with this. i have attached a sample log the real log has about 10 worksheet for diffrent departments but i only need info from the Veneer Log Sheet.
View 14 Replies
View Related
May 2, 2008
I want to create a standard macro that will copy values from a Master workbook to the current, active workbook I have open. The problem I have run into is when I record a macro it also records the name of the active workbook I'm copying data into and I would like it so when I bring up a workbook I just run the macro and it copies the values in. I'll be doing 8 different macros that each copy different values from the Master spreadsheet
View 8 Replies
View Related
Jan 25, 2009
I have a workbook with about 25 sheets. All the sheets are named. I'd like either a Macro or some VBA code whcih, when the user clicks a button will Unhide selected sheets, say "Equip Labor", "Equipment", Proj. Summary" and "Implementation Guide" and then copy these sheets into a new workbook created on the fly (Book1.xls) and then in the original workbook re-hide all the selected sheets. This way the user can save the new workbook as whatever name they want.
Is this possible? The closest post I could find was this:
http://www.mrexcel.com/forum/showthr...opy+Worksheets
I tried to modify this, but I'm not that good yet with VBA.
View 9 Replies
View Related
Mar 30, 2009
I have a code that will allow me to copy three worksheets from an open workbook to a new workbook. That works great, but I only need only a selected range from each of these worksheets to be copied (Range A1:AV60). Here is the code I use to select and copy the worksheets, how do I add a range within this code for each worksheet.
Sheets(Array("Req Page 1", "Req Ext 1", "Req Ext 2")).Copy
View 9 Replies
View Related
Sep 13, 2007
I am trying to create a method to select the values of contiguous range selections on excel worksheets.
Users will be making new worksheets in new workbooks out of the data from the old sheets.
Currently users make a selection is and copy it to a new worksheet.
Is it possible to retrieve from the 'clipboard', the values from the associated cells?
And then to parse them into columns in a new worksheet?
Earlier I tried this bit of ( it didnt work )
Sub AddNew()
' for passing highlighted text into the form so that I can pass it
'to a new workbook.
Dim PassData1 As String
Selection.Value = PassData1
Set NewBook = Workbooks.Add
With NewBook
.Title = "xxx"
. SaveAs Filename:="xxx.xls"
End With
Range("A1").Value = PassData1
End Sub
View 9 Replies
View Related
Apr 27, 2014
I am using this code to copy columns C,D, and P from a user selected workbook into position C,D,E of workbook "Checklist.xlsx" (the one from were I run the VBA code). I don't have a clue about VBA, just have been gathering info, but the code works pretty fine. The only issue is that it opens twice the workbook "wbExt" hence the system prompts me if i want to reopen the already open workbook.
Some questions:
1) Is there a way to copy the 3 columns at once? something like "Columns("C:D, P").Select".
2) Is there a way to select the workbook from were to copy the column without needing it to actually open?
3) is there a way to paste the columns starting from row 3 instead of row 1 (Range("C1").Select)?
View 2 Replies
View Related
Oct 13, 2009
As the buyer edits orders, I have a macro which pulls out all the parts with changes and puts them on a sheet named "Changes" in the attached "Sample" workbook. Since the buyer will do dozens of these edits a day, I'm trying to create a simple way for them to create a list of parts they may want to try and buy from another source.
I would like to add a macro that would open a message box telling the buyer to select the parts they want to copy. They could select whatever rows they want to copy to the "Backorder List", and select "OK" or hit "Enter", and the macro would open the workbook named "Backorder List.xls" on their desktop, (mine is located under "C:Documents and Settingsjerry.hutchesonDesktop"), and copy the selected rows to the workbook, then save changes and close the "List" workbook. It should only paste the values, to preserve whatever formatting is in the book being copied TO, and it should copy below any data already in the workbook.
To make things even more difficult, the macro would also need to copy the Vendor # and PO # from the top of the "Sample" workbook, (cells B2 and B3 respectively), and enter that data beside the other data just pasted beside the parts data in the backorder list. (I have entered sample data in the backorder list to show what I mean.)
View 2 Replies
View Related
Jun 22, 2008
I have a workbook with 30 some sheets. I would like to have a macro that if i select multiple sheets, will paste the values and formats of those sheets into a new workbook. I would like this to keep the names of the worksheets when transfered to the new workbook.
Thus far, I have been using this code, which does nearly everything i want, but instead of pasting the values, it gives me #VALUE! for nearly all the cells. Most of the cells are using Vlookup and/or Indirect functions to reference other sheets- not sure if this is relevant. The few cells that do paste accurately are either text or simply reference another cell on a different worksheet without a function.
Here's my code.
Sub PasteShtVal()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
View 9 Replies
View Related
