New Sheet Macro

[Jim]

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?

 

[Joel]

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]

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]

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]

Joel,

Brilliant, it works.

Can you please give me the code to transfer the data as per my original
post, I can then modify it for all the other data to transfer.

Thanks

 

[Joel]

Don't understand. The new sheet is identical to the old sheet so why move
data between the sheets. wouldn't it just be better to copy the data on the
new sheet to a different column?

This is what is confusing me!!!!!!!!!!!!!!!! what is new and old works? do
yo have any named ranges?

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.

 

[Jim]

The sheets are weekly reports that are cumalative. I want to add in new
values in range H10:H20 and print the report. When I run the macro, I want
it to take these new works and add to old works values in range G10:G20 and
put the new total in old works in the new sheet ready to add new works and
then repeat for each report.

 

[Joel]

See if this is what you want. I used copy and pastespecial to do the
addition. do you need to clear the range H10:H20. Is added in as the last
lkine incase you needed it

'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

NewSht.Range("H10:H20").Copy
NewSht.Range("G10").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlAdd
NewSht.Range("H10:H20").ClearContents

 

[Jim]

Thanks mate, that did the trick!

See if this is what you want. I used copy and pastespecial to do the
addition. do you need to clear the range H10:H20. Is added in as the last
lkine incase you needed it

'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

NewSht.Range("H10:H20").Copy
NewSht.Range("G10").PasteSpecial _
Paste:=xlPasteAll, _
Operation:=xlAdd
NewSht.Range("H10:H20").ClearContents

 

[Jim]

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?