Copy worksheets without any code

[PM]

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

 

[stanshoe]

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

 

[Peter T]

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

 

[Bernie Deitrick]

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