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 values>saves as>deletes macros.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"LiAD" <(E-Mail Removed)> wrote in message
news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
> 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 Tools>References

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

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
(E-Mail Removed)
"Don Guillett" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Perhaps a macro that change all formulas to values>saves as>deletes
> macros.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "LiAD" <(E-Mail Removed)> wrote in message
> news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
>> 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 Tools>References
>
> 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 Tools>References
>
> 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
> (E-Mail Removed)
> "Don Guillett" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > Perhaps a macro that change all formulas to values>saves as>deletes
> > macros.
> >
> > --
> > Don Guillett
> > Microsoft MVP Excel
> > SalesAid Software
> > (E-Mail Removed)
> > "LiAD" <(E-Mail Removed)> wrote in message
> > news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
> >> 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 Tools>References
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
(E-Mail Removed)
"LiAD" <(E-Mail Removed)> wrote in message
news:1847A9F2-CEA8-4A26-8E2E-(E-Mail Removed)...
> 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 Tools>References
>>
>> 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 Tools>References
>>
>> 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
>> (E-Mail Removed)
>> "Don Guillett" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > Perhaps a macro that change all formulas to values>saves as>deletes
>> > macros.
>> >
>> > --
>> > Don Guillett
>> > Microsoft MVP Excel
>> > SalesAid Software
>> > (E-Mail Removed)
>> > "LiAD" <(E-Mail Removed)> wrote in message
>> > news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
>> >> 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 Tools>References
> 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
> (E-Mail Removed)
> "LiAD" <(E-Mail Removed)> wrote in message
> news:1847A9F2-CEA8-4A26-8E2E-(E-Mail Removed)...
> > 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 Tools>References
> >>
> >> 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 Tools>References
> >>
> >> 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
> >> (E-Mail Removed)
> >> "Don Guillett" <(E-Mail Removed)> wrote in message
> >> news:%(E-Mail Removed)...
> >> > Perhaps a macro that change all formulas to values>saves as>deletes
> >> > macros.
> >> >
> >> > --
> >> > Don Guillett
> >> > Microsoft MVP Excel
> >> > SalesAid Software
> >> > (E-Mail Removed)
> >> > "LiAD" <(E-Mail Removed)> wrote in message
> >> > news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
> >> >> 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
(E-Mail Removed)
"LiAD" <(E-Mail Removed)> wrote in message
news:17609B68-21E4-4B48-9BB1-(E-Mail Removed)...
> 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 Tools>References
>> 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
>> (E-Mail Removed)
>> "LiAD" <(E-Mail Removed)> wrote in message
>> news:1847A9F2-CEA8-4A26-8E2E-(E-Mail Removed)...
>> > 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 Tools>References
>> >>
>> >> 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 Tools>References
>> >>
>> >> 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
>> >> (E-Mail Removed)
>> >> "Don Guillett" <(E-Mail Removed)> wrote in message
>> >> news:%(E-Mail Removed)...
>> >> > Perhaps a macro that change all formulas to values>saves as>deletes
>> >> > macros.
>> >> >
>> >> > --
>> >> > Don Guillett
>> >> > Microsoft MVP Excel
>> >> > SalesAid Software
>> >> > (E-Mail Removed)
>> >> > "LiAD" <(E-Mail Removed)> wrote in message
>> >> > news:419679C9-93B9-4BA8-A9B0-(E-Mail Removed)...
>> >> >> 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
>> >> >
>> >>
>> >>
>>
>>