Copy sheets without macros

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am running a workbook as a template that after the various macros have run
copies all the sheets to a new workbook and then saves it down to a new name.
The problem is it copies all the code too, i do not want to copy the code
across. I am using the code as below and would appreciate advice as to how
to stop the code being copied too.

Dim NewWb As Workbook
Dim ws As Worksheet
Dim i As Integer
Dim NumberSheets As Integer


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlManual

NumberSheets = Sheets.Count

'creates a new wb:
Sheets(Sheets.Count).Copy
Set NewWb = ActiveWorkbook

For i = NumberSheets - 1 To 1 Step -1
ThisWorkbook.Sheets(i).Copy Before:=NewWb.Sheets(1)
Next i
'Replace Formula with Values:
For Each ws In NewWb.Worksheets
ws.Cells.Copy
ws.Cells.PasteSpecial (xlPasteValues)
ws.Select
ws.Cells(1, 1).Select
Next ws
 
Hi Spike

You can delete the code in your sheet modules with Chip's code
http://www.cpearson.com/excel/vbe.htm

This is working OK for the activeworkbook
No reference needed in the example

Read the note on Chip's site about "Trust access to Visual Basic Project"

Public Sub DeleteAllVBA()
Dim VBComp As Object
Dim VBComps As Object
Set VBComps = ActiveWorkbook.VBProject.VBComponents
For Each VBComp In VBComps
Select Case VBComp.Type
Case 1, 3, _
2
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp
End Sub
 
Thjank you very much for your help/reply i think in view of Chuip's comments
re virus scanners etc i foresee big probs so think i will just have to live
with it or just copy and paste each sheet.
 
You can add a new worksheet each time with code and then copy all cells in this new sheet.
This way you not copy the code

If you need help with the code post back
 
Back
Top