Auto Paste Values

L

LiAD

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
 
D

Don Guillett

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
 
L

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
 
D

Don Guillett

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
 
L

LiAD

Yes I checked the extensibility option.

Any other possible reasons why I would get this?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top