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

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

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

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

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

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