In the past I was using the attached VBA to separate each tab into its own workbook and was saving it to one file path (referenced in one of my sheets) and then dragging each file to its proper folder. So is there a way from me to save each new workbook based on its name to a specific folder. I have all of the references file paths layout in one of my sheets like I was doing befor but, I’m unsure how to get this to work with 40 different sheets?
Code:
Sub ExportWeekly()
myOrigWkb = ActiveWorkbook.Name
Sheets("Directions").Select
myFilePath = Range("A17").Value
myPeriod = Range("A20").Value
For Each shtnext In Sheets
shtnext.Activate
If Right(ActiveSheet.Name, 6) = "Review" Then
ActiveSheet.Select
ActiveSheet.Copy
Rows("8:8").Select
Range(Selection, Selection.End(xlDown)).Select
Rows("8:331").EntireRow.AutoFit
Range("F8").Activate
mySubject = Range("C6").Value
myLocNum = Range("B1").Value
myLoc = Range("B2").Value
'Save File
myFileName = myFilePath & mySubject & " " & myDate & ".xlsx"
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = False
Workbooks(myOrigWkb).Activate
Sheets("Directions").Activate
End If
Next shtnext
End Sub
Last edited by a moderator: