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

Spike

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" <(E-Mail Removed)> wrote in message news:2A81AAE4-3433-41F9-AEB1-(E-Mail Removed)...
>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
> --
> with kind regards
>
> Spike

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

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" <(E-Mail Removed)> wrote in message news:F0B70CD7-C49B-4766-BF87-(E-Mail Removed)...
> 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 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
>> --
>> with kind regards
>>
>> Spike

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" <(E-Mail Removed)> wrote in message news:F0B70CD7-C49B-4766-BF87-(E-Mail Removed)...
> > 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 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
> >> --
> >> with kind regards
> >>
> >> Spike
>