VBA copy active worksheet, rename and paste to end

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
What exactly is it that you are trying to do?
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,610
Office Version
2016
Platform
Windows
Try this

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

End Sub
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
60
Office Version
2016
Platform
Windows
Thank you Trevor. So can I add something to where it changes the name of the new sheet to the new value in A1?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
How many new sheets do you want to create at any one time?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Then I don't see what the problem is, or will the date in A1 always remain the same?
 

RedOctoberKnight

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
Will you be changing the date in A1 on those sheets?
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
60
Office Version
2016
Platform
Windows
yes, so on the new sheet it creates, the new date in A1 would be +7 days from the previous sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,669
Office Version
365
Platform
Windows
In that case there will not be a problem, as long as you always copy the latest sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,351
Messages
5,486,357
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top