Hey James,
try this code for remove
Sub RemoveAllCode()
On Error Resume Next
'Make reference to Extensibilty Library
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 4, 0
On Error GoTo 0
Dim VBComp As Object, AllComp As Object, ThisProj As Object
Dim ThisRef, WS As Worksheet, Dlg As DialogSheet
Set ThisProj = ActiveWorkbook.VBProject
Set AllComp = ThisProj.VBComponents
For Each VBComp In AllComp
With VBComp
Select Case .Type
Case 1, 2, 3
AllComp.Remove VBComp
Case vbext_ct_Document
.CodeModule.DeleteLines 1, .CodeModule.CountOfLines
End Select
End With
Next
For Each ThisRef In ThisProj.References
If Not ThisRef.BuiltIn Then ThisProj.References.Remove ThisRef
Next
Application.DisplayAlerts = False
For Each WS In Excel4MacroSheets
WS.Delete
Next
For Each Dlg In DialogSheets
Dlg.Delete
Next
End Sub
On Thu, Aug 11, 2011 at 12:54 AM, ja...@macstop.co.uk
<ja...@macstop.co.uk> wrote:
> thanks - it's a long story but basically I'm trying to export some sheets
> from a workbook with loads of code behind it. I wanted to use move/copy
> originally but it takes the vba behind the sheets with it. (Which I don't
> want).
>
> So I wrote a function that just deleted all vba code in the workbook - but
> then I ran into Excel 2010 issues having to tick the VBA project security
> box to let that run.
>
> The guy I am working for won't want to tell all his users they have to tick
> that... and I was worried about backwards compatibility too, so I decided to
> go for the easier option of copying the cells rather than the sheet.
>
> It's working now, not particularly quick but I think it's quick enough!!
>
> Thanks for your help
>
>
>
> --
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
.........................
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
