Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy worksheets | Excel Discussion (Misc queries) | |||
how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
Copy Worksheets | Excel Worksheet Functions | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
VBA Copy Worksheets | Excel Programming |