| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Visual Basic - Variable Sheet Names
Hi Everyone,
I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
ALWAYS post your macro for comments
-- Don Guillett Microsoft MVP Excel SalesAid Software "MarkT" wrote in message ... Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Use the sheet codename.
If you look at the project window with a worksheet selected, you will see there are two names, like Sheet1(Sheet1). the first is the codename, the second is the Excel name. The codename remains even if the Excel name is changed. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MarkT" wrote in message ... Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
If the code is under the worksheet (not in a general module, not behind the
ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Sorry Don; here is my macro:
Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Don Guillett" wrote: ALWAYS post your macro for comments -- Don Guillett Microsoft MVP Excel SalesAid Software "MarkT" wrote in message ... Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Hi Bob, I don't see the sheet codename; I may not be looking at the macro as
you have descibed. Here is the macro: Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Bob Phillips" wrote: Use the sheet codename. If you look at the project window with a worksheet selected, you will see there are two names, like Sheet1(Sheet1). the first is the codename, the second is the Excel name. The codename remains even if the Excel name is changed. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "MarkT" wrote in message ... Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Hi Dave,
The macro is in the general module. Where would I insert your code, right before the "Sheets("Sheet1") .Select line? Here is the macro as I failed to submit it originally. Thanks for your help! Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Dave Peterson" wrote: If the code is under the worksheet (not in a general module, not behind the ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
If you want to run the code to copy the activesheet, you would delete the
"Sheets("sheet1").select" line. Then all the ranges that have the contents cleared will refer to the newly created sheet. ps. range("E2:g2").select selection.clearcontents can be replaced with: range("e2:g2").clearcontents And you can do this kind of thing, too: range("e2:g2,k2,i7,m7,e9,g12").clearcontents MarkT wrote: Hi Dave, The macro is in the general module. Where would I insert your code, right before the "Sheets("Sheet1") .Select line? Here is the macro as I failed to submit it originally. Thanks for your help! Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Dave Peterson" wrote: If the code is under the worksheet (not in a general module, not behind the ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Dave,
I deleted the line as you suggested, but it still stops at the next line of code where again it has "sheet1" in the code. I need to select the active sheet first, and copy it, then clear the various cells in the new sheet, leaving the old sheet in the workbook as it was. Is there any "Select active sheet" command available? The macro works if the current sheet is named Sheet1; if I could just replace the "sheet1" in the macro with whatever the current name of the sheet was I should be good to go. Thanks, Mark "Dave Peterson" wrote: If you want to run the code to copy the activesheet, you would delete the "Sheets("sheet1").select" line. Then all the ranges that have the contents cleared will refer to the newly created sheet. ps. range("E2:g2").select selection.clearcontents can be replaced with: range("e2:g2").clearcontents And you can do this kind of thing, too: range("e2:g2,k2,i7,m7,e9,g12").clearcontents MarkT wrote: Hi Dave, The macro is in the general module. Where would I insert your code, right before the "Sheets("Sheet1") .Select line? Here is the macro as I failed to submit it originally. Thanks for your help! Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Dave Peterson" wrote: If the code is under the worksheet (not in a general module, not behind the ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson -- Dave Peterson |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Sorry. You have to change this line, too:
Sheets("Sheet1").Copy Befo=Sheets(1) to Activesheet.Copy Befo=Sheets(1) And you have to manually select that sheet to be copied first--if you're going to rely on the activesheet code. MarkT wrote: Dave, I deleted the line as you suggested, but it still stops at the next line of code where again it has "sheet1" in the code. I need to select the active sheet first, and copy it, then clear the various cells in the new sheet, leaving the old sheet in the workbook as it was. Is there any "Select active sheet" command available? The macro works if the current sheet is named Sheet1; if I could just replace the "sheet1" in the macro with whatever the current name of the sheet was I should be good to go. Thanks, Mark "Dave Peterson" wrote: If you want to run the code to copy the activesheet, you would delete the "Sheets("sheet1").select" line. Then all the ranges that have the contents cleared will refer to the newly created sheet. ps. range("E2:g2").select selection.clearcontents can be replaced with: range("e2:g2").clearcontents And you can do this kind of thing, too: range("e2:g2,k2,i7,m7,e9,g12").clearcontents MarkT wrote: Hi Dave, The macro is in the general module. Where would I insert your code, right before the "Sheets("Sheet1") .Select line? Here is the macro as I failed to submit it originally. Thanks for your help! Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Dave Peterson" wrote: If the code is under the worksheet (not in a general module, not behind the ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson -- Dave Peterson -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Visual Basic - Variable Sheet Names
Dave, that works great; thank you very much!
"Dave Peterson" wrote: Sorry. You have to change this line, too: Sheets("Sheet1").Copy Befo=Sheets(1) to Activesheet.Copy Befo=Sheets(1) And you have to manually select that sheet to be copied first--if you're going to rely on the activesheet code. MarkT wrote: Dave, I deleted the line as you suggested, but it still stops at the next line of code where again it has "sheet1" in the code. I need to select the active sheet first, and copy it, then clear the various cells in the new sheet, leaving the old sheet in the workbook as it was. Is there any "Select active sheet" command available? The macro works if the current sheet is named Sheet1; if I could just replace the "sheet1" in the macro with whatever the current name of the sheet was I should be good to go. Thanks, Mark "Dave Peterson" wrote: If you want to run the code to copy the activesheet, you would delete the "Sheets("sheet1").select" line. Then all the ranges that have the contents cleared will refer to the newly created sheet. ps. range("E2:g2").select selection.clearcontents can be replaced with: range("e2:g2").clearcontents And you can do this kind of thing, too: range("e2:g2,k2,i7,m7,e9,g12").clearcontents MarkT wrote: Hi Dave, The macro is in the general module. Where would I insert your code, right before the "Sheets("Sheet1") .Select line? Here is the macro as I failed to submit it originally. Thanks for your help! Sheets("Sheet1").Select Sheets("Sheet1").Copy Befo=Sheets(1) Range("E2:G2").Select Selection.ClearContents Range("K2").Select Selection.ClearContents Range("E7").Select Selection.ClearContents Range("I7").Select Selection.ClearContents Range("M7").Select Selection.ClearContents Range("E9").Select Selection.ClearContents Range("I9").Select Selection.ClearContents Range("M9").Select Selection.ClearContents Range("G12").Select Selection.ClearContents Range("M12").Select Selection.ClearContents Range("E20:F20").Select Selection.ClearContents Range("I20").Select Selection.ClearContents Range("G22:M22").Select Selection.ClearContents Range("D24:M24").Select Selection.ClearContents Range("S7").Select Selection.ClearContents Range("W7:X7").Select Selection.ClearContents Range("S9:AA9").Select Selection.ClearContents Range("Q11:AA12").Select Selection.ClearContents Range("S14").Select Selection.ClearContents Range("Y14").Select Selection.ClearContents Range("Q17:AA17").Select Selection.ClearContents Range("Q18:AA19").Select Selection.ClearContents Range("E2").Select End Sub "Dave Peterson" wrote: If the code is under the worksheet (not in a general module, not behind the ThisWorkbook module), then you could use the Me keyword to refer to the worksheet owning the code. If the code is in a General module, but the worksheet is the active worksheet, you could use: with activesheet .range("a1").value = .name end with MarkT wrote: Hi Everyone, I have a macro that I run on a 2007 workbook that makes a duplicate copy of the current sheet by hitting a button with the macro associated with it. The macro runs fine when the sheet is named "Sheet1"; however, once a duplicate sheet has been created, I need to change the name of that sheet, then when I go and hit my macro button again, I get an error since I have now renamed "Sheet1" to something else. In VB, is there a term that I may use to duplicate the current sheet selected? I need to have a variable term used instead of the current "sheet1" that is in my macro. Thanks for your help as always! Mark -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| visual basic. find copy row past into new sheet | Excel Worksheet Functions | |||
| Visual Basic | Excel Discussion (Misc queries) | |||
| Visual Basics - Automating Specific Sheet Names | Excel Discussion (Misc queries) | |||
| changing the visual basic in office 2003 to visual studio net | Excel Discussion (Misc queries) | |||
| Visual Basic | Excel Discussion (Misc queries) | |||
Linear Mode
