Copy worksheets
 

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?


---
Message posted from http://www.ExcelForum.com/

Copy worksheets
 

Sheets.copy

Assumes no code in the sheet modules.

--
Regards,
Tom Ogilvy

"courtesio99" wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/

Copy worksheets
 

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


---
Message posted from http://www.ExcelForum.com/

Copy worksheets
 

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

--
Regards,
Tom Ogilvy

courtesio99 wrote in message
...
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


---
Message posted from http://www.ExcelForum.com/

Copy worksheets
 

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?


---
Message posted from http://www.ExcelForum.com/

Copy worksheets
 

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

--
Regards,
Tom Ogilvy

courtesio99 wrote in message
...
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?


---
Message posted from http://www.ExcelForum.com/