VBA copy active worksheet, rename and paste to end

RedOctoberKnight

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

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,589
Office Version
365
Platform
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
60
Office Version
2016
Platform
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,610
Office Version
2016
Platform
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
42,589
Office Version
365
Platform
Windows
@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,610
Office Version
2016
Platform
Windows
Good point Fluff, the op didn't mention having hidden sheets though.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,589
Office Version
365
Platform
Windows
Agreed, but then he didn't say there weren't any. ;)
 

RedOctoberKnight

Board Regular
Joined
Nov 16, 2015
Messages
60
Office Version
2016
Platform
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
42,589
Office Version
365
Platform
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
60
Office Version
2016
Platform
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,102,238
Messages
5,485,573
Members
407,504
Latest member
inexperiencedOne

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top