Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets without any code
Hi !
To achieve such a task, I start by copying the sheets like this : Set XLS = ThisWorkbook XLS.Sheets(1).Copy Set ARR = ActiveWorkbook For i = 3 To 9 XLS.Sheets(i).Copy After:=ARR.Sheets(ARR.Sheets.Count) Next This does a good job of copying fast and eliminating code modules (macros). But the code inside the sheets remains (such as SelectionChange, etc.). End users receiving these sheets would be confronted with countless error messages. I therefore resolve to delete the sheets' code with the following : With ARR.VBProject ... etc. see below if interested End With Well, upon hitting the first of these lines, I get error message 1004 "Accessing the VB Project by program is unreliable". Upon another go (F8) : the VBProject method ... has failed. Then I thought (foolishly...) that I might perhaps fix this by saving the target workbook first. The program then passes the critical line (first line above) only to jump later in the same routine flying past the end with (last line above) and even past an exit sub and landing after the first label. This is way beyond my limited capacity. Any miracle doctors recognize these symptoms ? Have a fix, one way or another ? I would be most grateful... Pat The entire code I used (which originates from this newsgroup, I believe) : With ARR.VBProject For Each O In .VBComponents Select Case O.Type Case 1, 2 ' standard or class module MsgBox "Module " & O.Name .VBComponents.Remove O Case Else ' form or document With O.CodeModule If .CountOfLines 0 Then MsgBox "Module behind " & O.Name .DeleteLines 1, .CountOfLines End If End With End Select Next End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets without any code
This article might help. It has code for programatically removing VBA code.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=93 Stan Shoemaker Palo Alto, CA "PM" wrote: Hi ! To achieve such a task, I start by copying the sheets like this : Set XLS = ThisWorkbook XLS.Sheets(1).Copy Set ARR = ActiveWorkbook For i = 3 To 9 XLS.Sheets(i).Copy After:=ARR.Sheets(ARR.Sheets.Count) Next This does a good job of copying fast and eliminating code modules (macros). But the code inside the sheets remains (such as SelectionChange, etc.). End users receiving these sheets would be confronted with countless error messages. I therefore resolve to delete the sheets' code with the following : With ARR.VBProject ... etc. see below if interested End With Well, upon hitting the first of these lines, I get error message 1004 "Accessing the VB Project by program is unreliable". Upon another go (F8) : the VBProject method ... has failed. Then I thought (foolishly...) that I might perhaps fix this by saving the target workbook first. The program then passes the critical line (first line above) only to jump later in the same routine flying past the end with (last line above) and even past an exit sub and landing after the first label. This is way beyond my limited capacity. Any miracle doctors recognize these symptoms ? Have a fix, one way or another ? I would be most grateful... Pat The entire code I used (which originates from this newsgroup, I believe) : With ARR.VBProject For Each O In .VBComponents Select Case O.Type Case 1, 2 ' standard or class module MsgBox "Module " & O.Name .VBComponents.Remove O Case Else ' form or document With O.CodeModule If .CountOfLines 0 Then MsgBox "Module behind " & O.Name .DeleteLines 1, .CountOfLines End If End With End Select Next End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets without any code
Hi Pat,
Could you copy Cells, something like this: Sub Test() For i = 1 To 3 Worksheets(i).Cells.Copy Sheets.Add(, Worksheets(Worksheets.Count)).Paste Next Application.CutCopyMode = False End Sub If you need to do something else to each new sheet (eg rename), set the newly added sheet to a variable before pasting. Regards, Peter "PM" wrote in message ... Hi ! To achieve such a task, I start by copying the sheets like this : Set XLS = ThisWorkbook XLS.Sheets(1).Copy Set ARR = ActiveWorkbook For i = 3 To 9 XLS.Sheets(i).Copy After:=ARR.Sheets(ARR.Sheets.Count) Next This does a good job of copying fast and eliminating code modules (macros). But the code inside the sheets remains (such as SelectionChange, etc.). End users receiving these sheets would be confronted with countless error messages. I therefore resolve to delete the sheets' code with the following : With ARR.VBProject ... etc. see below if interested End With Well, upon hitting the first of these lines, I get error message 1004 "Accessing the VB Project by program is unreliable". Upon another go (F8) : the VBProject method ... has failed. Then I thought (foolishly...) that I might perhaps fix this by saving the target workbook first. The program then passes the critical line (first line above) only to jump later in the same routine flying past the end with (last line above) and even past an exit sub and landing after the first label. This is way beyond my limited capacity. Any miracle doctors recognize these symptoms ? Have a fix, one way or another ? I would be most grateful... Pat The entire code I used (which originates from this newsgroup, I believe) : With ARR.VBProject For Each O In .VBComponents Select Case O.Type Case 1, 2 ' standard or class module MsgBox "Module " & O.Name .VBComponents.Remove O Case Else ' form or document With O.CodeModule If .CountOfLines 0 Then MsgBox "Module behind " & O.Name .DeleteLines 1, .CountOfLines End If End With End Select Next End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy worksheets without any code
PM,
Another approach is to insert a blank worksheet(s) using the Worksheets.Add method, then copy the cells of the Sheet(1) and paste them onto the newly inserted sheet(s). See the code below for a modification to your code to achievew this. This also assumes that your Sheet template doesn't have any code in it. HTH, Bernie MS Excel MVP Sub Macro1() Dim mySht1 As Worksheet Dim mySht2 As Worksheet Dim XLS As Workbook Dim ARR As Workbook Dim i As Integer Set XLS = ThisWorkbook Set ARR = ActiveWorkbook Set mySht1 = XLS.Sheets("Sheet1") For i = 1 To 7 Set mySht2 = ARR.Sheets.Add(Type:="Worksheet") mySht1.Cells.Copy mySht2.Cells Next i End Sub "PM" wrote in message ... Hi ! To achieve such a task, I start by copying the sheets like this : Set XLS = ThisWorkbook XLS.Sheets(1).Copy Set ARR = ActiveWorkbook For i = 3 To 9 XLS.Sheets(i).Copy After:=ARR.Sheets(ARR.Sheets.Count) Next This does a good job of copying fast and eliminating code modules (macros). But the code inside the sheets remains (such as SelectionChange, etc.). End users receiving these sheets would be confronted with countless error messages. I therefore resolve to delete the sheets' code with the following : With ARR.VBProject ... etc. see below if interested End With Well, upon hitting the first of these lines, I get error message 1004 "Accessing the VB Project by program is unreliable". Upon another go (F8) : the VBProject method ... has failed. Then I thought (foolishly...) that I might perhaps fix this by saving the target workbook first. The program then passes the critical line (first line above) only to jump later in the same routine flying past the end with (last line above) and even past an exit sub and landing after the first label. This is way beyond my limited capacity. Any miracle doctors recognize these symptoms ? Have a fix, one way or another ? I would be most grateful... Pat The entire code I used (which originates from this newsgroup, I believe) : With ARR.VBProject For Each O In .VBComponents Select Case O.Type Case 1, 2 ' standard or class module MsgBox "Module " & O.Name .VBComponents.Remove O Case Else ' form or document With O.CodeModule If .CountOfLines 0 Then MsgBox "Module behind " & O.Name .DeleteLines 1, .CountOfLines End If End With End Select Next End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i copy a cell in worksheets 10 to the other 9 worksheets | New Users to Excel | |||
copy between worksheets does not copy formulae just values | Excel Discussion (Misc queries) | |||
Copy Paste Special Value using Code over Several Worksheets | Excel Programming | |||
Code to copy range vs Copy Entire Worksheet - can't figure it out | Excel Programming | |||
Need code to protect worksheets - amount of worksheets varies | Excel Programming |