Saveas Excel without macros

[Rome75]

I have few macros developing and genarting worksheets and charts.

Is there a way to saveas the xls to a new xls file with just data and not macros in the way that the user will just be able to see the results and not formulas and macros who produced it??

Right now I just think I can do this writing in a VBA code a kind of copy sheets and paste just values and consuquently generating charts again but I hope there is a faster way.

I appreciate any help or hint.

Thanks
Fernando

 

[xlbo]

See if this works for you

put

Option Base 1
as the 1st line in your module

then:
sub CopyAll()
Dim shArr() As Variant
ReDim shArr(Sheets.Count)
For i = 1 To Sheets.Count
shArr(i) = Sheets(i).Name
Next i
Sheets(shArr()).copy
activeworkbook.saveas thisworkbook.name & " - without macros"
end sub

This will copy all sheets into a new workbook but will not overwrite the formulae with values - to do that, you could use:

For each sht in activeworkbook.worksheets
sht.cells.copy
sht.pastespecial paste:=xlpastevalues
next

after the last line of the previous sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[taupirho]

Try this:

Sub savenomacros()

ThisWorkbook.Sheets(Array("sheet1", "sheet2", "sheet3&quot).Copy

'
' Prevent popup message if SaveAs below is overwriting an existing file
'
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs FileName:="C:\nomacros.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

ActiveWorkbook.Close False

Application.Quit


End Sub

 

[TonyJollans]

Hi Rome75,

I'm not sure but I think that copying a sheet copies its code module as well. But wouldn't it be a whole lot easier not to have the code in the first place?

If you have macros generating worksheets and charts why not generate them in a separate workbook to start with?

Enjoy,
Tony

 

[Chattin]

You could call this procedure during your save as proc - from

http://www.cpearson.com/excel/vbe.htm

Deleting All VBA Code In A Project

The procedure below will delete all the VBA code in a project. You should use this procedure with care, as it will permanently delete the code. Standard modules, user forms, and class modules will be removed, and code within the ThisWorkbook module and the sheet modules will be deleted.

Sub DeleteAllVBA()

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

HTH