Save As Sheet to two different workbooks one with filters applied

JohnnyPicnic

New Member
Joined
Feb 8, 2021
Messages
6
Office Version
  1. 2019
Platform
  1. 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:

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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Larry Haydn

Board Regular
Joined
Jul 18, 2019
Messages
138
Office Version
  1. 365
Platform
  1. Windows
Suggestion:
1. Export that worksheet to a new workbook. Save into the desired workbook name.
2. Apply filter to the exported worksheet, SaveAs another workbook name.
 

Forum statistics

Threads
1,136,800
Messages
5,677,809
Members
419,721
Latest member
StuckInWork

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top