Increasing the Sheet #

[Guest]

Hi all,

I have an Excel sheet that links to Access database. I have created a macro
to copy the entire linked sheet and to turn the copied sheet into value.

One of my macro lines is to rename the copied sheet into "Workable Schedule
1". Here is the line:

Sheets("Linked Sheet (2)").Name = "Workable Schedule 1"

What I like to do is to make the macro to increment each copied sheet by 1
when I click on the command button on the linked sheet. For example,
"Workable Schedule 1", "Workable Schedule 2", etc.

Please help.

Thanks.

 

[tissot.emmanuel]

Hi,

Try this:

Sub InsertRenameSheet()
Dim NewSheet As Worksheet, n As Integer
Sheets("Linked Sheet").Copy after:=Sheets(Sheets.Count)
Set NewSheet = Sheets(Sheets.Count)
n = 1
On Error Resume Next
Do
Err.Clear
NewSheet.Name = "Workable Schedule " & n
n = n + 1
Loop While Err.Number > 0
On Error GoTo 0
End Sub

Note that if one of the copied sheet is deleted the number will be re-used
at the next run.

Regards,

Manu/

 

[Guest]

Good morning Manu,

Thanks for your help. Your code works perfectly. Thanks again.