About Us

Don Guillett

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Change paste to only paste values	Chris Trygstad	Excel Programming	1	November 6th 08 08:07 PM
find values in multiple cells and paste row values	izzyt1972	Excel Discussion (Misc queries)	5	December 26th 07 10:14 PM
can you change the default paste method? (paste values)	David A Brown	Excel Discussion (Misc queries)	3	December 18th 07 09:59 AM
Auto-lookup values between Sheet3 & UserForm & paste to Sheet1	duBedat68	Excel Programming	2	December 22nd 04 01:37 PM
How do i compare values from two sheet and copy & paste if values match?	rozb	Excel Programming	0	March 5th 04 12:06 AM

Menu

About Us