Sheetcopy macro

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Vamplew

New Member
Joined
Nov 9, 2011
Messages
38
I'm trying to copy a file, put it on the end of my tabs, and automatically rename it to the current date.
 

Silvanus

New Member
Joined
Oct 28, 2011
Messages
43
Not sure exactly what you mean, but this will copy a sheet into another workbook and save it where you specify.

Code:
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

New Member
Joined
Oct 28, 2011
Messages
43

ADVERTISEMENT

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

Code:
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

New Member
Joined
Nov 9, 2011
Messages
38
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

New Member
Joined
Nov 9, 2011
Messages
38

ADVERTISEMENT

Any other ideas?
 

Silvanus

New Member
Joined
Oct 28, 2011
Messages
43
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

New Member
Joined
Oct 28, 2011
Messages
43
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,576
Messages
5,596,992
Members
414,115
Latest member
SFUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top