VBA copy active worksheet, rename and paste to end

[RedOctoberKnight]

Good Morning,

I'm messing around on a scratch workbook trying to teach myself a little VBA. I'm trying to accomplish the following. I'd like to copy the current worksheet and paste it to the end of the workbook while renaming it based on the value in cell A1. After doing some googling I've found the following code that renames based on a specified text.

Sub Sample()
ActiveSheet.Copy , Sheets(Sheets.Count)
Active Sheet.Name = "copied sheet"
End Sub

I'd like it to rename it based on a date in cell A1 but if possible, I would like it to add 7 days.

So in other words, I'd like it to copy the current worksheet, paste it to the end of the workbook while renaming it 7 days from the original date in cell A1.

Any help would be much appreciated.

Thanks!

 

[RedOctoberKnight]

Sorry for being a pain but how do I do that?

 

[Fluff]

Just use the code from post#2 & ensure that the latest sheet is the active sheet when you run it.

 

[RedOctoberKnight]

it works when I only have one sheet but once I select the sheet it just built and run it again, it gives me a same name error.

Sub Sample()
ActiveSheet.Copy , Sheets(Sheets.Count)
ActiveSheet.Name = Format(Range("A1").Value + 7, "dd_mm_yy")

End Sub


When i get the error msg and I hit end, it creates a new sheet but the date is still the same and its just added a (2) or (3) to the name.

 

[Fluff]

In that case you have not changed the date in A1, which you said you would be doing.

 

[RedOctoberKnight]

I think I figured it out guys. Thank you so much for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.