Copying A Worksheet Only, Excluding the Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
Back
Top