Sheetcopy macro

[Vamplew]

Does anybody have the code for the sheet copy macro

 

[buxtongt]

Be more specific about what you are wanting to do.

 

[Vamplew]

I'm trying to copy a file, put it on the end of my tabs, and automatically rename it to the current date.

 

[Silvanus]

Not sure exactly what you mean, but this will copy a sheet into another workbook and save it where you specify.

Sub AAA()
 
Sheets("Sheet1").Copy
 
Set wbNew = ActiveWorkbook

With wbNew
    ActiveWorkbook.SaveAs Filename:="Insert File path here" & ".xls", FileFormat:=xlnormal _
    , CreateBackup:=False
End With
 
End Sub

 

[Silvanus]

Sorry, didnt read the updates before I posted. Try:

Sub AAA()
   Dim Sh As Worksheet
   Worksheets("Sheet1").Copy after:=Worksheets(Worksheets.Count)
   Set Sh = ActiveSheet
   Sh.Name = "Sheet1" & Format(Now, "mm/dd/yyyy")
End Sub

If you don't want the sheet name in there, I believe you can replace "Sheet1" with "" after the Sh.Name portion of the code.

 

[Vamplew]

I'm doing a spreadsheet as a school project. to get an a* my teacher says I need to copy the spreadsheet (sheet1) to the end of my tab. And have the file renamed as the date. He says I need the formula for a macro called sheet copy.

 

[Vamplew]

Any other ideas?

 

[Silvanus]

No such thing?

I've been pounding code out for a while, and he's probably talking about the portion of the code: Sheets("whateversheetname").copy

 

[Silvanus]

let me try a few things and get back to you

 

[Silvanus]

This does exactly what you are asking. Just change ("Sheet1") to whatever sheet name you need it to be, and it will copy it, move it to the end of the workbook, and rename it for the current date.

Sub AAA()
   Dim Sh As Worksheet
   [COLOR=red]S[/COLOR][COLOR=red]heets("Sheet1").Copy[/COLOR] after:=Sheets(Sheets.Count)
   Set Sh = ActiveSheet
   Sh.Name = Format(Now, "mm-dd-yyyy")
End Sub

Let me know if it works for you