Copy sheets without macros
 

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 Befo=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
--
with kind regards

Spike

Copy sheets without macros
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Spike" wrote in message ...
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 Befo=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
--
with kind regards

Spike

Copy sheets without macros
 

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.
--
with kind regards

Spike


"Spike" wrote:

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 Befo=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
--
with kind regards

Spike

Copy sheets without macros
 

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
--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Spike" wrote in message ...
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.
--
with kind regards

Spike


"Spike" wrote:

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 Befo=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
--
with kind regards

Spike

Copy sheets without macros
 

Many thanks for that, think i will do it like that
--
with kind regards

Spike


"Ron de Bruin" wrote:

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
--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Spike" wrote in message ...
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.
--
with kind regards

Spike


"Spike" wrote:

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 Befo=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
--
with kind regards

Spike