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!

 

[Fluff]

How about

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

 

[RedOctoberKnight]

thank you.

It works the first time but when I run it again, I get a runtime error 1004 "name is already taken"

 

[Trevor G]

Try this:

Sub Sample1A()
ActiveSheet.Copy , Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
ActiveSheet.Name = Format(Range("A1").Value + 7, ("dd-mm-yy"))

End Sub

 

[Fluff]

@Trevor G
If the last sheet in the workbook is hidden your code will fail.

@RedOctoberKnight
What do you want to happen if the sheet already exists?

 

[Trevor G]

Good point Fluff, the op didn't mention having hidden sheets though.

 

[Fluff]

Agreed, but then he didn't say there weren't any.

 

[RedOctoberKnight]

no hidden sheets. Right now i'm just using a simple scratch workbook that has the date in A1 and a few bits of data in A2:A7.

It works once and then I continue to get a duplicate name taken error.

I want it to take the date in A1 and add 7 days to it. So if today is 7-21 then the new sheet will be 7-28, and then when I have the 7-28 sheet selected and run it, the next sheet will become 8-4 and so on.

 

[Fluff]

Unless you change the date in A1, then you are bound to get an error as the sheet name already exists.

 

[RedOctoberKnight]

so is there a way to just create a copy of the sheet, move it to the end while adding +7 to the date in Cell A1 and disregarding the sheet name change?