rpaulson
Well-known Member
- Joined
- Oct 4, 2007
- Messages
- 1,428
Im wanting to copy many sheets from my workbook and save then into a new workbook to act as a backup.
current code.
this code works but I have 2 issues.
1. The original worksheets have buttons on them assigned to code. I need these buttons removed in the backup file.
2. The original sheets have code that runs on sheet activation and cell change events that call other macros, therefor I get errors as this code is not in the new workbook (nor do I want it to be)
I am only really after the sheet values and formatting. to be copied.
Thanks for looking,
Ross
current code.
Code:
Sub Save_Backup_File()
Application.ScreenUpdating = False
Dim wbSave As Workbook
Set wbSave = ThisWorkbook
Dim wbNew As Workbook
myPath = "C:\Results\"
myfile = myPath & "Scoring Tower Backup " & Format(Now, "mm-dd-yy") & ".xlsx"
Set wbNew = Workbooks.Add
For Each ws In wbSave.Sheets
If ws.Visible = xlSheetVisible And ws.[A1] = "XXX" Then
ws.Copy after:=wbNew.Sheets(wbNew.Sheets.count)
End If
Next
wbNew.SaveAs FileName:=myfile
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
this code works but I have 2 issues.
1. The original worksheets have buttons on them assigned to code. I need these buttons removed in the backup file.
2. The original sheets have code that runs on sheet activation and cell change events that call other macros, therefor I get errors as this code is not in the new workbook (nor do I want it to be)
I am only really after the sheet values and formatting. to be copied.
Thanks for looking,
Ross