JohnnyPicnic
New Member
- Joined
- Feb 8, 2021
- Messages
- 17
- Office Version
- 2019
- Platform
- Windows
Hello, I would like to save a single sheet from a workbook twice. Once with no filters and once with filer applied.
I can't seem to figure out how to save the first file, close it then move onto the second save as.
This is what I have so far:
I can't seem to figure out how to save the first file, close it then move onto the second save as.
This is what I have so far:
VBA Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayAlerts = False
Dim wb As Workbook
Set wb = Workbooks.Add
'Save First Work Sheet as xlsx for Google Maps Import
ThisWorkbook.Sheets("Sites").Copy Before:=wb.Sheets(1)
If ThisWorkbook.Sheets("Sites").FilterMode Then
ThisWorkbook.Sheets("Sites").ShowAllData
End If
wb.SaveAs "Sites.xlsx", FileFormat:=xlOpenXMLWorkbook
'Save First Work Sheet with filter as xlsx for Google Maps Import
ThisWorkbook.Sheets("Sites").Copy Before:=wb.Sheets(1)
If ThisWorkbook.Sheets("Sites").FilterMode Then
ThisWorkbook.Sheets("Sites").ShowAllData
End If
wb.Sheets("Sites").Range("$A$1:$Q$121").AutoFilter Field:=14, Criteria1:="<>"
wb.SaveAs "Sites Work Only.xlsx", FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
Application.DisplayAlerts = True
End Sub