I made this code idiot proof which makes it a little bit more complicated. I
wasn't sure if the hight number sheet was going to be the last tabb in the
worksheet so I searched all the sheet to find the highest sheet number
starting with CERT.

I would put the control button i the tool bar rather than in the sheet so
you don't have to have the button on each sheet. Yo will see in the tool
bars a doulbe right arrow. Pressing the arrow wil allow you to add the
button to the tool bars.


'get highest sheet number
HighShtNum = 0
For Each Sht In Sheets
ShtName = Sht.Name
If UCase(Left(ShtName, 4)) = "CERT" Then
Num = Val(Trim(Mid(ShtName, InStr(ShtName, " "))))
If Num HighNum Then
HighNum = Num
End If
End If
Next Sht

Set OldSht = Sheets("Cert " & HighNum)
NewNum = HighNum + 1
OldSht.Copy after:=Sheets(Sheets.Count)
Set NewSht = ActiveSheet
NewSht.Name = "Cert " & NewNum


"Jim" wrote:

I would like to create a macro that will execute after clicking a command
button located on sheet1 (Named "Cert 1") that would copy sheet1 and rename
it "Cert 2". The command button would move from sheet1 to sheet2. Clicking
the command button again would now create sheet3 named "Cert 3" and again
move the command button to the newly created sheet. This would continue for
up to 36 sheets.

In addition to creating the sheet, I want this macro to move data from the
copied sheets 'new works' values (say range H10:H20) add it to 'old works'
values (say range G10:G20) and place it on the new sheet in the 'old works'
range. There would be several other calculations like this for the new
sheet, but once I get the basic code, I can add them.

Is this possible?