Recording Macro To Duplicate Page And Rename To Numeric Value [Solved/Closed]

Ask a question Shaun@1 2Posts Sunday March 13, 2016Registration date March 15, 2016 Last seen - Last answered on Mar 15, 2016 at 10:04 AM by Shaun@1
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"
ActiveCell.FormulaR1C1 = "188"
End Sub


Any help would be appreciated
Thank you in advanceiated
Thank you in advanceou in advance
plus moins
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,
Was this answer helpful?  
Shaun@1 2Posts Sunday March 13, 2016Registration date March 15, 2016 Last seen - Mar 15, 2016 at 10:04 AM
Hi TrowaD

This works perfectly.

Thank you very much I appreciate your help.

Kind regards

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!