Copy Sheets minus Worksheet Change Event code & Macro Buttons
 

Hi,

XL2K.

I have used the basis of the following code (courtesy of Tom Ogilvy, I
think) a number of times to copy sheets from one workbook to another
and save/name the 2nd workbook via a cell reference.

Private Sub CommandButton1_Click()
' Copies Certification, Current Quarter & Control History sheets to
' new workbook, then saves to identified path.
Unload UserForm5
Application.ScreenUpdating = False
On Error GoTo ErrHndler
Dim wkbk1 As Workbook
Dim wkbk2 As Workbook
Dim sh As Worksheet
Dim shts As Sheets
Set wkbk1 = ActiveWorkbook
Worksheets(Array("Certification", "Current Quarter", "Control
History")).Copy
Set wkbk2 = ActiveWorkbook
Set shts = wkbk2.Worksheets(Array("Certification", "Current
Quarter", "Control History"))
For Each sh In shts
sh.Unprotect Password:="password"
sh.Cells.Copy
sh.Cells.PasteSpecial xlValues
sh.Cells(1, 1).Select
sh.Protect Password:="password"
Next
'wkbk2.Protect Password:="password"
Sheets("Certification").Select
wkbk2.SaveAs Range("A1").Value
wkbk2.Close SaveChanges:=False
Application.GoTo wkbk1.Worksheets("Preparation
Guide").Range("A1"), Scroll:=False
Sheets("Preparation Guide").Unprotect Password:="password"
Range("C17").NumberFormat = "General"
Selection.NumberFormat = "General"
Range("A1").Select
Sheets("Preparation Guide").Protect Password:="password"
wkbk1.Protect Password:="password"
Application.ScreenUpdating = True
UserForm8.Show
Exit Sub
ErrHndler:
wkbk2.Close SaveChanges:=False
On Error GoTo 0
UserForm7.Show
End Sub

However on this occasion, one of the sheets ("Certification") has two
macro buttons and worksheet change event code attached. I do not wish
to copy the buttons or change event code to the new workbook.

Would someone be kind enough to suggest amendments to the code to
achieve this.

Cheers

Bob
Maitland Australia