VBA for referring to new user-defined workbook

[xcellrodeo]

Hello MrExcel Community I have an issue with a VBA script. I am trying to copy a spreadsheet from one workbook to a new user-defined Workbook. The script I have is as follows:

Workbooks("CART2013.xlsm").Activate
Sheets("Sales By Month").Select
Sheets("Sales By Month").Copy Before:=Workbooks("&T2&.xlsm").Sheets(1)

However, this doesn't work.Btw, T2 refers to an input box where the new file name is declared i.e Region 1.xlsm (but file names are can change). Also I am having trouble determining the correct code for copying the worksheet into the correct position in the new workbook.At present it overwrites whatever is on the 1st sheet in the new workbook. Ideally I would want the script to be modified so that the worksheet is copied into the new workbook at the very front of whatever tabs /sheets are there (i.e not overwriting them).Hope this helps.Any help is greatly appreciated.

 

[VoG]

Try

Workbooks("CART2013.xlsm").Activate
Sheets("Sales By Month").Copy Before:=Workbooks(T2 & ".xlsm").Sheets(1)

 

[xcellrodeo]

Thanks for the suggestion but I am still having problems getting the code to work. Here is the complete script which may be more helpful.#Sub CopyRankPage45()
Set NewBook = Workbooks.Add
'User-defined file name
T2 = InputBox("Enter a file name and file extention i.e 'Data.xlsm'")
'File Path to be saved in selected location
ChDir "C:\Users\M\Desktop\CAL - RANK\test"
ActiveWorkbook.SaveAs Filename:= _
"C:\Users\M\Desktop\CAL - RANK\test\ " & T2 & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

'Copy Rank Worksheet to new Workbook
Workbooks("CART2013.xlsm").Activate
Sheets("Sales By Month").Select
Sheets("Sales By Month").Copy Before:=Workbooks(T2 & ".xlsm").Sheets(1)
'Renaming
T1 = InputBox("Enter date of issue in the format 'Mar-13'")
Sheets("Sales By Month").Name = "Issued " & T1

#

 

[VoG]

If you are entering the complete file name including the extension try

ActiveWorkbook.SaveAs Filename:= _
"C:\Users\M\Desktop\CAL - RANK\test\" & T2, FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Note that I alo removed a space after test\

 

[xcellrodeo]

Thanks for your input. Did you have any suggestions on the issue described above concerning the script

Sheets("Sales By Month").Copy Before:=Workbooks(T2 & ".xlsm").Sheets(1)

which determines the position where the worksheet is placed in the new workbook? I am still having the problem that the worksheet is being copied correctly but overwrites any previous sheet in the workbook.Thanks

 

[VoG]

This should work

Sheets("Sales By Month").Copy Before:=Workbooks(T2).Sheets(1)

 

[xcellrodeo]

The above script does work but only for the first copy into a blank workbook. For example lets say the worksheet is named "Sales Apr-13". Lets say that the following month, another sales data worksheet with the name of "Sales May-13" is copied into the same workbook. Sales Apr-13 will now be overwritten with Sales May-13.I think this is because the following script specifies that any new worksheets are copied to the position immediately before sheets 1.So I would imagine the script would need to be modified so that any new worksheet is copied not before sheet 1 but before the last or first sheet in the range and to ensure that no sheet is overwritten if that makes sense. I look forward to reading some suggestions

 

[xlHammer]

I can't see any reason why your code should be overwriting anything, 'copy before' should do exactly that not overwrite or replace anything.</SPAN>

The only thing I can think of is that your code is creating a new workbook every time instead of opening the existing one once it has been created the first time. So what's happening is you're creating a new workbook, copying in the latest sheet then saving over the existing workbook, so it looks like you are overwriting it. Does that make sense?</SPAN>

 

[xcellrodeo]

Thanks for help.