Hello, I have a workbook with 5 tabs listed below:
- Dashboard
- Compare
- ADT file
- BEFORE
- AFTER
I would like to be able to write code that loops through each of the 5 worksheets but only save 3 of them to a new file location in a new workbook. The 3 worksheets that i would like to have saved into new workbook are 'Compare', 'BEFORE', and 'AFTER'. This is the code I have so far:
Application.DisplayAlerts = False
Dim worksheet_list As Variant 'this may not apply
Dim wb As Workbook
Dim Path As String
Dim Fname As String
Dim ws As Worksheet
Dim wsdash As Worksheet
Dim wsADT As Worksheet
Dim MacroSheet As Worksheet
worksheet_list = Array("Compare", "BEFORE", "AFTER") ' this may not apply'
Set wb = ThisWorkbook
Set ws = wb.Sheets("BEFORE")
Set wsdash = Worksheets("Dashboard")
Set wsADT = Worksheets("ADT File")
Path = "G:\Cayton Files\Excel Macro\"
Fname = ws.Range("A2") & " " & "Restriction APU Review " & Format(Now(), "YYYY-MM-DD") & ".xlsx"
Workbooks.Add
For Each MacroSheet In wb.Sheets
If MacroSheet.Name <> wsdash.Name Or wsADT Then
MacroSheet.Copy Before:=ActiveWorkbook.Sheets("Sheet1")
End If
Next MacroSheet
ActiveWorkbook.SaveAs Filename:=Path & Fname
ActiveWorkbook.Close
Application.DisplayAlerts = True
The error message occurs when i get to this statement: If MacroSheet.Name <> wsdash.Name Or wsADT Then
That's where im stuck. I found a way to just save the entire new workbook (with all 5 sheets) and then just delete the sheets i dont want, however i feel like there may be a more efficient way of doing this (ex: looping through the 5 and only pulling the 3 that i need).
So my question is, how would looping through each of the 5 sheets and only saving the 3 sheets i want into a new workbook work?
Any help is greatly appreciated!