Recording Macro To Duplicate Page And Rename To Numeric Value

Solved/Closed
Shaun@1 Posts 2 Registration date Sunday March 13, 2016 Status Member Last seen March 15, 2016 - Mar 13, 2016 at 04:19 AM
Shaun@1 Posts 2 Registration date Sunday March 13, 2016 Status Member Last seen March 15, 2016 - Mar 15, 2016 at 10:04 AM
Good day

I am trying to record a macro that duplicates a page and renaming it to another file name but need to follow in numeric order.

I have an 'Invoice' Excell workbook to invoice my clients.
My current page name is 'Invoice 187' obviously the next one should follow in numeri order: 'Invoice 188'.
I then also have a cell in the document that has the title 'Invoice' and the cell nect to it should have this number as well. The sell is E8.
I do not know how to do this, can someone please help?

What I have is the following:


"

' Keyboard Shortcut: Ctrl+p
'
Sheets("Invoice 187").Select
Sheets("Invoice 187").Copy After:=Sheets(1)
Sheets("Invoice 187 (2)").Select
Sheets("Invoice 187 (2)").Name = "Invoice 188"
Range("E8").Select
ActiveCell.FormulaR1C1 = "188"
End Sub

"


Any help would be appreciated
Thank you in advanceiated
Thank you in advanceou in advance
Related:

1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Mar 14, 2016 at 12:38 PM
Hi Shaun,

The following code will do as requested, even when you reach the 4 digits.
Just make sure you run the code from the latest invoice.

Sub RunMe()
Dim MyNumber As Integer
MyNumber = Right(ActiveSheet.Name, 4) + 1
ActiveSheet.Copy after:=Sheets(ActiveSheet.Name)
ActiveSheet.Name = "Invoice " & MyNumber
Range("E8").Value = MyNumber
End Sub



Best regards,
Trowa
1
Shaun@1 Posts 2 Registration date Sunday March 13, 2016 Status Member Last seen March 15, 2016
Mar 15, 2016 at 10:04 AM
Hi TrowaD

This works perfectly.

Thank you very much I appreciate your help.

Kind regards
Shaun
0