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

Report
Posts
2
Registration date
Sunday March 13, 2016
Status
Member
Last seen
March 15, 2016
-
Posts
2
Registration date
Sunday March 13, 2016
Status
Member
Last seen
March 15, 2016
-
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

1 reply

Posts
2674
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 27, 2020
446
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2
Registration date
Sunday March 13, 2016
Status
Member
Last seen
March 15, 2016

Hi TrowaD

This works perfectly.

Thank you very much I appreciate your help.

Kind regards
Shaun