| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying A Worksheet Only, Excluding the Code
Hello,
Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying A Worksheet Only, Excluding the Code
add a new sheet, then copy the cells to the new sheet.
or disable events copy the sheet remove the code in the copy http://www.cpearson.com/excel/vbe.htm enable events -- Regards, Tom Ogilvy "MWS" wrote in message ... Hello, Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying A Worksheet Only, Excluding the Code
Tom, Thank You for the reply. I tried the process of adding a new sheet
before I posted the question, but ran into this issue. I have the following code: Sheets("Populate Scorecard....").Select Sheets.Add Sheets("Populate Scorecard....").Select Cells.Select Selection.Copy Sheets("Sheet4").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1:I1").Select In this code, the first time it is executed, "sheet4" contains the copy of "Populate Scorecard" and there are no issues. How can I edit the code, so each time the user decides they need a copy of "Populate Scorecard", any previous copies remain and a new worksheet is added, then the contents can be copied to the most recently created worksheet? In the code, I believe I have to make "sheet4" a variable or refer to it differently, to accomodate this, but cannot figure out how to do it. Thanks Again for Your Help "Tom Ogilvy" wrote: add a new sheet, then copy the cells to the new sheet. or disable events copy the sheet remove the code in the copy http://www.cpearson.com/excel/vbe.htm enable events -- Regards, Tom Ogilvy "MWS" wrote in message ... Hello, Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying A Worksheet Only, Excluding the Code
Dim sh as Worksheet
With ThisWorkbook ' or ActiveWorkbook set sh = .Worksheets.Add( After:= _ .Worksheets(.Worksheets.count)) .Worksheets("Populate Scorecard....").Cells.copy _ Destination:=Sh.Cells ' optional 'sh.activate End With -- Regards, Tom Ogilvy "MWS" wrote in message ... Tom, Thank You for the reply. I tried the process of adding a new sheet before I posted the question, but ran into this issue. I have the following code: Sheets("Populate Scorecard....").Select Sheets.Add Sheets("Populate Scorecard....").Select Cells.Select Selection.Copy Sheets("Sheet4").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1:I1").Select In this code, the first time it is executed, "sheet4" contains the copy of "Populate Scorecard" and there are no issues. How can I edit the code, so each time the user decides they need a copy of "Populate Scorecard", any previous copies remain and a new worksheet is added, then the contents can be copied to the most recently created worksheet? In the code, I believe I have to make "sheet4" a variable or refer to it differently, to accomodate this, but cannot figure out how to do it. Thanks Again for Your Help "Tom Ogilvy" wrote: add a new sheet, then copy the cells to the new sheet. or disable events copy the sheet remove the code in the copy http://www.cpearson.com/excel/vbe.htm enable events -- Regards, Tom Ogilvy "MWS" wrote in message ... Hello, Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying A Worksheet Only, Excluding the Code
But didn't you say you wanted to go to a different workbook? Assume
ABCD.xls is the destination workbook Dim sh as Worksheet Dim bk as Workbook set bk = Workbooks("ABCD.xls") With bk ' or ActiveWorkbook set sh = .Worksheets.Add( After:= _ .Worksheets(.Worksheets.count)) End With ThisWorkbook.Worksheets("Populate Scorecard....").Cells.copy _ Destination:=Sh.Cells ' optional ' bk.Activate ' sh.activate -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim sh as Worksheet With ThisWorkbook ' or ActiveWorkbook set sh = .Worksheets.Add( After:= _ .Worksheets(.Worksheets.count)) .Worksheets("Populate Scorecard....").Cells.copy _ Destination:=Sh.Cells ' optional 'sh.activate End With -- Regards, Tom Ogilvy "MWS" wrote in message ... Tom, Thank You for the reply. I tried the process of adding a new sheet before I posted the question, but ran into this issue. I have the following code: Sheets("Populate Scorecard....").Select Sheets.Add Sheets("Populate Scorecard....").Select Cells.Select Selection.Copy Sheets("Sheet4").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1:I1").Select In this code, the first time it is executed, "sheet4" contains the copy of "Populate Scorecard" and there are no issues. How can I edit the code, so each time the user decides they need a copy of "Populate Scorecard", any previous copies remain and a new worksheet is added, then the contents can be copied to the most recently created worksheet? In the code, I believe I have to make "sheet4" a variable or refer to it differently, to accomodate this, but cannot figure out how to do it. Thanks Again for Your Help "Tom Ogilvy" wrote: add a new sheet, then copy the cells to the new sheet. or disable events copy the sheet remove the code in the copy http://www.cpearson.com/excel/vbe.htm enable events -- Regards, Tom Ogilvy "MWS" wrote in message ... Hello, Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copying A Worksheet Only, Excluding the Code
Tom, Thank You for your time. I think this will work and will be able to try
it later, but I wanted to pass on the "Thanks". Take Care "Tom Ogilvy" wrote: But didn't you say you wanted to go to a different workbook? Assume ABCD.xls is the destination workbook Dim sh as Worksheet Dim bk as Workbook set bk = Workbooks("ABCD.xls") With bk ' or ActiveWorkbook set sh = .Worksheets.Add( After:= _ .Worksheets(.Worksheets.count)) End With ThisWorkbook.Worksheets("Populate Scorecard....").Cells.copy _ Destination:=Sh.Cells ' optional ' bk.Activate ' sh.activate -- Regards, Tom Ogilvy "Tom Ogilvy" wrote in message ... Dim sh as Worksheet With ThisWorkbook ' or ActiveWorkbook set sh = .Worksheets.Add( After:= _ .Worksheets(.Worksheets.count)) .Worksheets("Populate Scorecard....").Cells.copy _ Destination:=Sh.Cells ' optional 'sh.activate End With -- Regards, Tom Ogilvy "MWS" wrote in message ... Tom, Thank You for the reply. I tried the process of adding a new sheet before I posted the question, but ran into this issue. I have the following code: Sheets("Populate Scorecard....").Select Sheets.Add Sheets("Populate Scorecard....").Select Cells.Select Selection.Copy Sheets("Sheet4").Select Cells.Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1:I1").Select In this code, the first time it is executed, "sheet4" contains the copy of "Populate Scorecard" and there are no issues. How can I edit the code, so each time the user decides they need a copy of "Populate Scorecard", any previous copies remain and a new worksheet is added, then the contents can be copied to the most recently created worksheet? In the code, I believe I have to make "sheet4" a variable or refer to it differently, to accomodate this, but cannot figure out how to do it. Thanks Again for Your Help "Tom Ogilvy" wrote: add a new sheet, then copy the cells to the new sheet. or disable events copy the sheet remove the code in the copy http://www.cpearson.com/excel/vbe.htm enable events -- Regards, Tom Ogilvy "MWS" wrote in message ... Hello, Background I have a worksheet labelled "Populate Scorecard", that has an interface/GUI and several calculations run upon the "activate" event. Issue There is now a need to present the user with a screen/GUI, giving them the option of whether or not they want to copy the "Populate Scorecard"worksheet. I'm copying the worksheet via: Sheets("Populate Scorecard....").Select Sheets("Populate Scorecard....").Copy After:=Sheets(5) Since the sheets is copied, when the user then activates the copied sheet (ie "Populate Scorecard (2)"), the calculations are rerun, which I must prevent. Question Is there a way to systematically copy a worksheet, without the associated code also being copied? Any responses, or alternative approaches, will be greatly appreciated. Thanks In Advance |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Printing worksheet excluding rows with formulas | Excel Programming | |||
| Automatic copying data excluding blank cells | Excel Worksheet Functions | |||
| Print current worksheet excluding a few cells | Excel Programming | |||
| help with excluding 0s from code | Excel Programming | |||
| Problem copying worksheet containing code module | Excel Programming | |||
Linear Mode
