Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated every
week I could like to save all the data formatted in exactly the same way etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another pre
assigned file with the same layout and no macros - thats no problem. However
it is, or will be a long long macro which means if I need to change it will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

Perhaps a macro that change all formulas to valuessaves asdeletes macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated
every
week I could like to save all the data formatted in exactly the same way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another pre
assigned file with the same layout and no macros - thats no problem.
However
it is, or will be a long long macro which means if I need to change it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

This should do as I described. FAST. Change filename to suit. You will need
to establish the reference first.
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Perhaps a macro that change all formulas to valuessaves asdeletes
macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated
every
week I could like to save all the data formatted in exactly the same way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another pre
assigned file with the same layout and no macros - thats no problem.
However
it is, or will be a long long macro which means if I need to change it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

Hi,

Thanks for your help.

Neat solution I like it!! 1000x better than the long winded copy paste
macro I had in mind.

Several questions if possible please -

a) The macro is telling me Run time error 1004, Programmatic access to VB
project is not trusted. The error is appearing in the delete VB
section at the
following line:

Set VBComps = ActiveWorkbook.VBProject.VBComponents

b) There are two sheets one called Details and the other Machines that I
would like
to delete completely, tab included. Is this possible to add in?

c) There are several form buttons for the users use to run the macros - can
these
also be deleted with code?

d) The above steps will leave 9 sheet tabs. 8 of which from columns A10 to
B500
have cells that are coloured either red, green or nothing (white). Is
it possible
to have a paste special in the code to copy the colours in each of
these cells to
the new file? (either that or keep the contents of the cells (its 0,1
or "") and the
conditional format).

I guess u can see my VB skills/knowledge is pretty skinny. Sorry as I guess
some of these things are simple when u know how.

Thanks again
LiAD

"Don Guillett" wrote:

This should do as I described. FAST. Change filename to suit. You will need
to establish the reference first.
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Perhaps a macro that change all formulas to valuessaves asdeletes
macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated
every
week I could like to save all the data formatted in exactly the same way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another pre
assigned file with the same layout and no macros - thats no problem.
However
it is, or will be a long long macro which means if I need to change it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

I mentioned this twice. Did you do it?
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences
on the shapes, try

sub delshapes()
for each sh in activeworkbook.shapes
sh.delete
next sh
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

Thanks for your help.

Neat solution I like it!! 1000x better than the long winded copy paste
macro I had in mind.

Several questions if possible please -

a) The macro is telling me Run time error 1004, Programmatic access to VB
project is not trusted. The error is appearing in the delete VB
section at the
following line:

Set VBComps = ActiveWorkbook.VBProject.VBComponents

b) There are two sheets one called Details and the other Machines that I
would like
to delete completely, tab included. Is this possible to add in?

c) There are several form buttons for the users use to run the macros -
can
these
also be deleted with code?

d) The above steps will leave 9 sheet tabs. 8 of which from columns A10
to
B500
have cells that are coloured either red, green or nothing (white). Is
it possible
to have a paste special in the code to copy the colours in each of
these cells to
the new file? (either that or keep the contents of the cells (its 0,1
or "") and the
conditional format).

I guess u can see my VB skills/knowledge is pretty skinny. Sorry as I
guess
some of these things are simple when u know how.

Thanks again
LiAD

"Don Guillett" wrote:

This should do as I described. FAST. Change filename to suit. You will
need
to establish the reference first.
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Perhaps a macro that change all formulas to valuessaves asdeletes
macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated
every
week I could like to save all the data formatted in exactly the same
way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another
pre
assigned file with the same layout and no macros - thats no problem.
However
it is, or will be a long long macro which means if I need to change it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

Yes I checked the extensibility option.

Any other possible reasons why I would get this?

"Don Guillett" wrote:

I mentioned this twice. Did you do it?
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences
on the shapes, try

sub delshapes()
for each sh in activeworkbook.shapes
sh.delete
next sh
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

Thanks for your help.

Neat solution I like it!! 1000x better than the long winded copy paste
macro I had in mind.

Several questions if possible please -

a) The macro is telling me Run time error 1004, Programmatic access to VB
project is not trusted. The error is appearing in the delete VB
section at the
following line:

Set VBComps = ActiveWorkbook.VBProject.VBComponents

b) There are two sheets one called Details and the other Machines that I
would like
to delete completely, tab included. Is this possible to add in?

c) There are several form buttons for the users use to run the macros -
can
these
also be deleted with code?

d) The above steps will leave 9 sheet tabs. 8 of which from columns A10
to
B500
have cells that are coloured either red, green or nothing (white). Is
it possible
to have a paste special in the code to copy the colours in each of
these cells to
the new file? (either that or keep the contents of the cells (its 0,1
or "") and the
conditional format).

I guess u can see my VB skills/knowledge is pretty skinny. Sorry as I
guess
some of these things are simple when u know how.

Thanks again
LiAD

"Don Guillett" wrote:

This should do as I described. FAST. Change filename to suit. You will
need
to establish the reference first.
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Perhaps a macro that change all formulas to valuessaves asdeletes
macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is updated
every
week I could like to save all the data formatted in exactly the same
way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another
pre
assigned file with the same layout and no macros - thats no problem.
However
it is, or will be a long long macro which means if I need to change it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD

macro security.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Yes I checked the extensibility option.

Any other possible reasons why I would get this?

"Don Guillett" wrote:

I mentioned this twice. Did you do it?
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences
on the shapes, try

sub delshapes()
for each sh in activeworkbook.shapes
sh.delete
next sh
end sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

Thanks for your help.

Neat solution I like it!! 1000x better than the long winded copy paste
macro I had in mind.

Several questions if possible please -

a) The macro is telling me Run time error 1004, Programmatic access to
VB
project is not trusted. The error is appearing in the delete VB
section at the
following line:

Set VBComps = ActiveWorkbook.VBProject.VBComponents

b) There are two sheets one called Details and the other Machines that
I
would like
to delete completely, tab included. Is this possible to add in?

c) There are several form buttons for the users use to run the
macros -
can
these
also be deleted with code?

d) The above steps will leave 9 sheet tabs. 8 of which from columns
A10
to
B500
have cells that are coloured either red, green or nothing (white).
Is
it possible
to have a paste special in the code to copy the colours in each of
these cells to
the new file? (either that or keep the contents of the cells (its
0,1
or "") and the
conditional format).

I guess u can see my VB skills/knowledge is pretty skinny. Sorry as I
guess
some of these things are simple when u know how.

Thanks again
LiAD

"Don Guillett" wrote:

This should do as I described. FAST. Change filename to suit. You will
need
to establish the reference first.
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Sub copyclean()
'cleans formulas
For Each ws In Worksheets
ws.UsedRange.Value = ws.UsedRange.Value
Next ws
'savesAS
ActiveWorkbook.SaveAs Filename:="200905011_B", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
DeleteAllVBA
End Sub

Sub DeleteAllVBA()
'You will need to make a reference to Microsoft Visual Basics for
'Applications Extensibility under ToolsReferences

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
.DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
Perhaps a macro that change all formulas to valuessaves asdeletes
macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
Hi,

I have a file with about 10 worksheets in it, each with a lot of
data,
formulas, macros etc - as a result the file is a chunky 16MB.

In order not to take up huge amounts of space when the file is
updated
every
week I could like to save all the data formatted in exactly the
same
way
etc
but without the formulas and macros into another separate file.

I can create a macro to do a copy/paste special values into another
pre
assigned file with the same layout and no macros - thats no
problem.
However
it is, or will be a long long macro which means if I need to change
it
will
be a nightmare.

Is there any quicker way of doing this?

Thanks
LiAD