copying workbooks

  • Thread starter Thread starter alistair01
  • Start date Start date
A

alistair01

I have a macro which copies sheets from a workbook and saves it a
another, the problem is that it copies all the vba code as well whe
all i want is the values. The problem with the code is that alot of i
is in the workbook open event. The code is below

Sub Report()

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Alarm Analysis").Activate
ActiveWorkbook.RefreshAll

Sheets("Main Menu").Select

Dim DataWorkbook As Workbook
Dim SaveFileName As String

If MsgBox("Do you want to create a Report?", vbQuestion + vbYesNo
= vbYes Then
Application.Run "Info"
Set DataWorkbook = ActiveWorkbook

Sheets(Array("Alarm Analysis", "Alarm Type-Door", "Main Menu"
"Clearance Times", "Alarm Type-Intruder", _
"Alarm Type-Fire", "Alarm Type-Panic"
"Clearance-Incident Acknowledged", _
"Clearance-False Alarm", "Clearance-System Test"
"Clearance-User Input", _
"Alarm Activations", "Programs Activated", "Ful
Report", "AlarmClearancesC", "AlarmclTimeC", "AlarmtypeC"
"AlarmClearanceC", "ProgramActivationsC")).Copy

SaveFileName = "C:\Documents an
Settings\ajohnston01.VMSGLAS.000\My Documents\Reports\Report_"
format(Now, "dd_mm_yyyy") & ".xls"
ActiveWorkbook.SaveAs Filename:=SaveFileName
MsgBox "New Report Created and Saved As " & SaveFileName
'Application.Run ("ActiveWorkbook_Zip_Mail")

If MsgBox("Would you like to view the current report?"
vbQuestion + vbYesNo) = vbNo Then
ActiveWorkbook.Close
Workbooks("Titan Vision.xls").Activate
Application.Run "Clearall"
Application.Run "clearClearanceTimes"
Sheets("Main Menu").Select
End If

Else
Workbooks("Titan Vision.xls").Activate
'ActiveWorkbook.Close
Sheets("Main Menu").Select

End If
Application.Run ("UserScreen")
Sheets("Main Menu").Activate



End Sub

thanks in advanc
 
i am not sure but i think your macro is copying the sheet
macros. if they were general then it wouldn't do that. and
i don't know how to stop it. you may have to delete the
macros in the report.

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

this site give instruction and code examples on how to
delete macros with code.
best i can do.
 
Back
Top