Joel,

I have run into a snag. Everything works well until I renamed the workbook.
I will have multiple copies of this work book for different project. The
macro button I added to the tool bar wants to run the macro from the original
workbook, not the currently opened one. Funny enough it works in the open
workbook, but it opens the original workbook first as long as it can find the
original workbook. When I renamed the original it would fail altogether.

Any suggestions?

"Joel" wrote:

That sounds right. I don't have 2007 availabe to me. I think being in the
toolbars is better than having a button on each sheet. Having the control
button on each sheet means you need a macro in each sheet which makes the
code more complicated.

"Jim" wrote:

Joel,

I guess I should have said I was using Excel 2007. I cannot seem to find
the double right arrows you talked about. Do you want me to add a button to
the quick access toolbar, or is it something else? If it is the quick access
toolbar, what button should I add?

Thanks

"Joel" wrote:

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?