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:

Sub CopySheet()
Dim fname As Variant, ws As Worksheet, wb As Workbook
Dim nCol As Integer, iCol As Integer


View 1 Replies


Copy A Sheet Into A New Book And Save File Name As The Current Date

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

Save File Under Current Name In A Selected Location

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

SaveAs - Copy Sheet Into New Workbook And Save It In Same Folder As Original Workbook

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"

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

VBA Create New Folder In Current Location And Save Selected Sheets As PDF

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.

Sub SaveWorksheetsAsPDFs()
Dim sFile As String
Dim sPath As String
Dim fPath As String
Dim wks As Worksheet

[Code] ........

View 3 Replies View Related

Macro To Copy Current Sheet, Create, & Rename New Sheet From Current Open Sheet

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

Save Copy Of Workbook, Leaving 1 Sheet Only

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

Copy From One Sheet To Another Sheet Then Save In Another Workbook

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.

Sub MoveData()
sName = "temp"
Const csPath As String = "C:Documents and SettingsmcgaulcDesktopTestFile"
'MyName = ActiveWorkbook.Name


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

VBA Copy From Sheet To New Workbook And Save As From Name In Target Cell Then Loop

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

Date - Sheet To Copy The Info To Another Workbook, Save And Close

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

Macro To Copy Dynamic Sheet In New Workbook - Save As Temp / Mail And Delete

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

Save Workbook As Current Date & Time

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

Save Workbook With Current File Name To Specific Folder

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

Macro Save Current Sheet

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()
End Sub

View 9 Replies View Related

Macro To Save Current Sheet As Tab Delimited

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

Copy Worksheet From Closed Workbook To Current Opened Workbook

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

Search Cell Value In Another Workbook And Copy Range From Current To Other Workbook

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

Autofilter Another Workbook And Copy Range Into Current Workbook

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

Copy Selected Range & Save To Text File

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

Copy Worksheets Into Current Workbook

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

Save Single Sheet To New Workbook And Delete VBA Code From New Workbook

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.

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")


View 2 Replies View Related

Macro To Create A Copy Of Current Workbook

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

Copy Rows From Selected Sheet To New Sheet Based On Cell Value

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

Copy Data From Open File To Current Active Workbook

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

Copy Selected Sheets To New Workbook

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:
I tried to modify this, but I'm not that good yet with VBA.

View 9 Replies View Related

Copy Selected Range To A New Workbook

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

Copy Selected Range To New Workbook

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

Copy Columns From One User Selected Workbook To Another

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

Copy Selected Data To Workbook On Desktop..

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

Copy Selected Worksheets As Values To New Workbook

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
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub

View 9 Replies View Related

Copyrights 2005-15, All rights reserved