copy/paste sheet into same workbook

[austin350s10]

How do I take one worksheet lets call it "Test1" copy it and paste it into the same workbook with a new name "Test1 - (todays date)"?

 

[pagrender]

Something like this? Test this with a copy of your file before you use it.


Sub CopySheetMacro()
'
' CopySheetMacro Macro
'
'
Sheets("Test1").Select
Sheets("Test1").Copy Before:=Sheets(1)
Sheets("Test1 (2)").Name = "Test1 - " & Format(Date, "mmm dd yy")
End Sub

 

[austin350s10]

Thats kinda what I was thinking but it makes current sheet use the new name format instead of the copied sheet.

I am trying to make it do this:

(fresh workbook sheets in order)

Test1, other1, other2

(run macro once sheet order)
Test1, Test1 - (date), other1, other2

(run macro again)
Test 1, Test1 - (date), Test1 -(date), other1, other2

......

Test 1 should always stay visible though

is there anyway to do something like this?

 

[VoG]

Possibly

Sheets("Test1").Copy after:=Sheets(Worksheets.Count)
Activeworkbook.saveas filename:-= Format(Date, "yyyy-mm-d") & ".xls"

 

[austin350s10]

Perfect....thanks again

 

[austin350s10]

What if I want to use a date from a cell on the same sheet the macro is being called from. I took a crack a the code below but on luck.

What am i doing wrong?

Dim assessmentDate As Date

assessmentDate = Range("L81").Value

Sheets("Needs Assessment").Copy after:=Sheets(1)
ActiveSheet.Name = "Initial Assessment - " & assessmentDate
Sheets("Needs Assessment").Select

 

[austin350s10]

Nevermind this works great:

Sheets("Needs Assessment").Copy after:=Sheets(1)
ActiveSheet.Name = "Initial Assessment - " & Format(Range("L81"), "mm-dd-yyyy")
Sheets("Needs Assessment").Select