Duplicate sheet with counting sheet name number

[Endarto]

Hi Sir,

i want to make macro to copy one sheet with nam PV-001 and for the new sheet the sheet name will change to PV-002. So when i push button "New PVB" it will create new sheet with new sheet name but it always continued counting the sheet name number.And for the new sheet, I want to put always at the very last Sheet. and this process will always continued from PV-001, PV-002, PV003,-,-,-.

Regards,
Endar

[HansV]

Welcome to Eileen's Lounge!

Assign this macro to the button:

Code: Select all

Sub NewPVB()
  Dim wsh As Worksheet
  Dim lngSeq As Long
  Dim lngMax As Long
  ' Find the highest number used
  For Each wsh In Worksheets
    If wsh.Name Like "PVB-*" Then
      lngSeq = CLng(Mid(wsh.Name, 5))
      If lngSeq > lngMax Then
        lngMax = lngSeq
      End If
    End If
  Next wsh
  ' Increment for new sheet
  lngMax = lngMax + 1
  ' Create new sheet
  Worksheets("PVB-001").Copy After:=Worksheets(Worksheets.Count)
  ' Rename the new sheet
  Worksheets(Worksheets.Count).Name = "PVB-" & Format(lngMax, "000")
End Sub

[Endarto]

Hello Sir,

Thanks for your nice and quick response.

your macro its working for Sheet "PVB". But on worksheet i have PVB, JVB and RVB.
When i copy your macro and change PVB to JVB or RVB

is there any solution for this?

Cheers,
Endarto

[HansV]

"When i copy your macro and change PVB to JVB or RVB" - then what is the problem?

[Endarto]

Hai Sir,

im so sorry...

The problem its when i change PVB to JVB or RVB, its not working.

but i already find the solution.

Thanks,
Endar

[HansV]

OK, glad you were able to solve it.

[Endarto]

Hi Hans,

Sorry to bother you again with my question.

After two week i work with my voucher, now i reach voucher more than 10. and now i have a problem with my voucher. this macro can running any more after some number. for example when reach CP-020 the macro is stop. it can not create another new sheet. i Attached my Voucher.

Thanks,
Endarto

[HansV]

When I try it, it works correctly up to CP-100:

x569.png

After that, there is an error message, but that can easily be repaired.

In your original post, you had sheet names with three letters, such as PVB-001. The number part of the name starts at the 5th character. That is the reason for the 5 in the line

Code: Select all

      lngSeq = CLng(Mid(wsh.Name, 5))

Your new workbook has sheet names with 2 letters such as CP-001. The number part of the name starts at the 4th character. So the above line should be changed to

Code: Select all

      lngSeq = CLng(Mid(wsh.Name, 4))

You will then be able to create sheets up to CP-1000.

[Rick Rothstein]

Your new workbook has sheet names with 2 letters such as CP-001. The number part of the name starts at the 4th character. So the above line should be changed to

Code: Select all

      lngSeq = CLng(Mid(wsh.Name, 4))

You will then be able to create sheets up to CP-1000.

Why not use this instead...

Code: Select all

lngSeq = CLng(Mid(wsh.Name, InStr(wsh.Name, "-") + 1))

and then you will not have to worry about how many characters are in front of the dash?
 

[HansV]

Yes, that's more generic!

[Endarto]

Hi Hans and Rick

i try to repair this case on Excel 2007, and its work, but when i try on Excel 2003 its still same.

Erorr.jpg

and if i press "Debug" the code is erorr in :

Sheets("JVE-000").Copy After:=Worksheets(Worksheets.Count)

Code: Select all

Sub NewJVE()
  Dim wsh As Worksheet
  Dim lngSeq As Long
  Dim lngMax As Long
  ' Find the highest number used
  For Each wsh In Worksheets
    If wsh.Name Like "JVE-*" Then
      lngSeq = CLng(Mid(wsh.Name, InStr(wsh.Name, "-") + 1))
      If lngSeq > lngMax Then
        lngMax = lngSeq
      End If
    End If
  Next wsh
  ' Increment for new sheet
  lngMax = lngMax + 1
  ' Create new sheet
   Sheets("JVE-000").Copy After:=Worksheets(Worksheets.Count)
  ' Rename the new sheet
  Worksheets(Worksheets.Count).Name = "JVE-" & Format(lngMax, "000")
End Sub

Is there any solution for for this, cause in my office we only have license for Excel 2003

Thank

Regards,
Endarto

[HansV]

I see no reason why the code would cause an error, but see if it helps if you change it as follows:

Code: Select all

Sub NewJVE()
  Dim wsh As Worksheet
  Dim lngSeq As Long
  Dim lngMax As Long
  ' Find the highest number used
  For Each wsh In Worksheets
    If wsh.Name Like "JVE-*" Then
      lngSeq = CLng(Mid(wsh.Name, InStr(wsh.Name, "-") + 1))
      If lngSeq > lngMax Then
        lngMax = lngSeq
      End If
    End If
  Next wsh
  ' Increment for new sheet
  lngMax = lngMax + 1
  On Error GoTo ErrHandler
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Application.EnableEvents = False
  ' Create new sheet
   Sheets("JVE-000").Copy After:=Worksheets(Worksheets.Count)
  ' Rename the new sheet
  Worksheets(Worksheets.Count).Name = "JVE-" & Format(lngMax, "000")

ExitHandler:
  Application.EnableEvents = True
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  Exit Sub

ErrHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

[PJ_in_FL]

...and if i press "Debug" the code is erorr in :

Sheets("JVE-000").Copy After:=Worksheets(Worksheets.Count)
...

I note that in the original post your template worksheet was numbered "001", but in the example above the template worksheet number has changed to "000". For the code to work, a sheet with the exact name of "JVE-000" must already exist in the worksheet before you run the macro. Please make sure the sheet with this name exists, or change the code to match the exact name of the template worksheet.