Macro to copy a worksheet and name the sheet 1 more than highest valued worksheet name in book

[JSlabach]

I have a workbook with one worksheet named 0 which will serve as a template and a worksheet named data.
I need help with a macro to copy sheet 0, a command button for this macro would be placed in sheet data.
Every time the macro button is pushed it would create an additional copy of sheet 0 in the same workbook,
each new sheet being automatically named one consecutive number higher than the highest numbered worksheet name currently in the book also putting that sheet name/number in cell h3 of each new sheet.
So I could push this button 100 times and end up with 100 copies of sheet 0, with the sheet names being numbered 1 through 100 and having the sheet name in each sheet's cell h3.

 

[AlphaFrog]

[COLOR=darkblue]Sub[/COLOR] Duplicate_Template_0()
    
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    Sheets("0").Copy After:=Sheets(Sheets.Count)
    [COLOR=darkblue]With[/COLOR] ActiveSheet
        .Name = Sheets.Count - 2
        .Range("H3").Value = .Name
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    Sheets("Data").Select
    Application.ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[JSlabach]

Thank you.
That seems way too simple.
I was definitely going about it the wrong way.
Just wondering what the part about application screen updating is for.
I've never seen that.

 

[hiker95]

Jslabach,

Here is sheet 0, with range E2:F4 simulating the command button to run the macro:

Excel Workbook
	A	B	C	D	E	F	G	H
1
2					Copy this Sheet
3								0
4
5	Macro to copy a worksheet
6	and name the sheet 1 more than
7	highest valued worksheet name in book
8
0

If you were to run the macro five times, you would have sheets 0, 1, 2, 3, 4, and 5:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2
3								5
4
5	Macro to copy a worksheet
6	and name the sheet 1 more than
7	highest valued worksheet name in book
8
5

If you were to delete say sheet 4, and then run the macro again (press the command button again), the next sheet that you would get would be sheet 6:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2
3								6
4
5	Macro to copy a worksheet
6	and name the sheet 1 more than
7	highest valued worksheet name in book
8
6

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run, sheet 0
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor

4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
4. a. If your command button is CommandButton1, then copy the code here.

5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub CommandButton1_Click()
' hiker95, 06/18/2012
' http://www.mrexcel.com/forum/showthread.php?641432-Macro-to-copy-a-worksheet-and-name-the-sheet-1-more-than-highest-valued-worksheet-name-in-book
' Macro to copy a worksheet and name the sheet 1 more than highest valued worksheet name in book.
Dim w0 As Worksheet, ws As Worksheet
Dim wsc As Long, wsm As Long
Dim lur As Long, luc As Long
Application.ScreenUpdating = False
Set w0 = Worksheets("0")
lur = w0.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
luc = w0.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
wsc = ThisWorkbook.Worksheets.Count
wsm = 0
If wsc = 1 Then
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CLng(wsm) + 1
  Set ws = ActiveSheet
  w0.Range(w0.Cells(1, 1), w0.Cells(lur, luc)).Copy ws.Cells(1, 1)
  ws.Cells(3, 8).Value = CLng(wsm) + 1
Else
  For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "0" Then
      If CLng(ws.Name) > wsm Then wsm = CLng(ws.Name)
    End If
  Next ws
  Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = CLng(wsm) + 1
  Set ws = ActiveSheet
  w0.Range(w0.Cells(1, 1), w0.Cells(lur, luc)).Copy ws.Cells(1, 1)
  ws.Cells(3, 8).Value = CLng(wsm) + 1
End If
w0.Activate
Application.ScreenUpdating = True
End Sub

Then click on the CommandButton1_Click button/macro again.