VBA copy active worksheet, rename and paste to end

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,345
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
   ActiveSheet.Copy , Sheets(Sheets.Count)
   ActiveSheet.Name = Format(Range("A1").Value + 7, "dd_mm_yy")
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Try this:

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

End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,345
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

@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

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Good point Fluff, the op didn't mention having hidden sheets though.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,345
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,345
Office Version
  1. 365
Platform
  1. Windows
Unless you change the date in A1, then you are bound to get an error as the sheet name already exists.
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
101
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,133,142
Messages
5,657,083
Members
418,352
Latest member
Jonjozz

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