Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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" 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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" wrote: 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" 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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" wrote: 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" wrote: 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" 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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" wrote: 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" wrote: 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" wrote: 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" 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
Thanks mate, that did the trick!
"Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
New Sheet Macro
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to lookup a sheet, number, then display in origonal sheet | New Users to Excel | |||
Help to code Macro to Copy fron one sheet and paste in other sheet | Excel Programming | |||
Q: Creating a macro to sort and group columns in a sheet according to another sheet | Excel Programming | |||
Return to Current Sheet in On (sheet activate) event macro | Excel Programming | |||
excel - macro code to open a protected sheet, enter passowrd, and then protect sheet | Excel Programming |