VBA for copying entire sheet into new workbook infront of other sheets

[xcellrodeo]

Hi there, I would appreciate some help concerning VBA script. Task: To copy an entire spreadsheet (right click & copy) to new workbook and place the copied spreadsheet infront any pre-existing sheets in the workbook (i.e not overwriting them). The script I currently have is:

Workbooks.Open ("C:\Users\M\Desktop\test1.xlsx") Workbooks("CalRan2013.xlsm").Activate Sheets("Ranking By Month").Select Sheets("Ranking By Month").Copy Before:=Workbooks("test1.xlsx").Sheets(1)
Workbooks("test1.xlsx").Activate
Sheets("Ranking By Month").Select
T1 = InputBox("Enter date of issue in the format 'Mar-13'") Sheets("Ranking By Month").Name = "Issued " & T1

The above script currently overwrites any new sheet copied in. Any help would be much appreciated thanks

 

[JLGWhiz]

See if this will work for you.

Set wb = Workbooks.Open("C:\Users\M\Desktop\test1.xlsx") Workbooks("CalRan2013.xlsm")
ThisWorkbook.Sheets("Ranking By Month").Copy Before:=wb.Sheets(1)
T1 = InputBox("Enter date of issue in the format 'Mar-13'")
wb.Sheets("Ranking By Month").Name = "Issued " & T1
wb.Close True