VBA copy active worksheet, rename and paste to end

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
150
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!
 
What exactly is it that you are trying to do?
 
Upvote 0

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Try this

VBA Code:
Sub Sample1A()
ActiveSheet.Copy , Sheets(Sheets.Count)
Sheets(Sheets.Count).Select
Range("A1").Value = Range("A1") + 7

End Sub
 
Upvote 0
Thank you Trevor. So can I add something to where it changes the name of the new sheet to the new value in A1?
 
Upvote 0
How many new sheets do you want to create at any one time?
 
Upvote 0
Then I don't see what the problem is, or will the date in A1 always remain the same?
 
Upvote 0
First off, I appreciated both of you for the help. It's very much appreciated.

My ultimate goal is to create a weekly inventory workbook. My idea is to have the current inventory in one column (B5:B21) and the previous week's in another (C5:C21). When I have it create a new worksheet, it will move the data from the current inventory over to the previous inventory column on the new worksheet and then zero out the current inventory column in the new worksheet. Inventory is done every Monday hence the 7 days. The sheet names would be the named the date the inventory was taken (every Monday). Sorry if I'm not explaining it very well.
 
Upvote 0
Will you be changing the date in A1 on those sheets?
 
Upvote 0
yes, so on the new sheet it creates, the new date in A1 would be +7 days from the previous sheet.
 
Upvote 0
In that case there will not be a problem, as long as you always copy the latest sheet.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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
Back
Top