Copy worksheets

  • Thread starter Thread starter courtesio99
  • Start date Start date
C

courtesio99

How can I copy all the worksheets in my current workbook to a new
workbook but I want to remove all the macros in the new workbook?

How can I write a code for it?
 
It's precisely the codes in the sheets that are causing me problems. Is
there a way to copy everything over to a new workbook without copying
any code over? I also need to maintain the print/page setup as well.

Thanks
 
You can copy the cells, but not the print/page setup.

Perhaps you would be better off locating your code in a separate workbook.
Or if you need controls on the sheets, you can use forms toolbar controls
rather than control toolbox toolbar controls. The code for these does not
need to be put in sheet modules.

You can also look at Chip Pearson's site for code to clear code.

http://www.cpearson.com/excel/vbe.htm
 
Suppose I copy the entire workbook to another workbook...
How do I write the code to delete the sheet code for Sheets 1 and 6 of
the new workbook?
 
Do you really just want Sheets 1 to 6 or to remove all code. If the latter:

This is an adaptation of code written and posted by Jim Rech:

Sub RemoveAllCodeFromCopy()
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3
Const vbext_ct_Document As Long = 100

Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef As Object, WS As Worksheet, DLG As DialogSheet
Dim ThisBook As Workbook
On Error Resume Next
Kill "C:\Data1\Backup.xls"
On Error GoTo 0
ThisWorkbook.SaveCopyAs "C:\Data1\Backup.xls"
Set ThisBook = Workbooks.Open("C:\Data1\Backup.xls")
Set ThisProj = ThisBook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case vbext_ct_StdModule, vbext_ct_ClassModule, _
vbext_ct_MSForm
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, _
.CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then _
ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each DLG In DialogSheets
DLG.Delete
Next
End Sub
 
Back
Top