This is a question geared towards optimization and general better coding practices. I'm self-taught in VBA so I'm cautious of bad practices, but they do come up.
I break the links (
break_links) because these new files would be sent out and I don't want the external references to cause errors within the spreadsheet formulas. It also makes for better historical files IMO so as the main file is updated we have the historical numbers saved rather than referencing the new numbers.
I did take this component from somewhere online, so I'm still unsure how things work when you put something like
(ByRef wb As Workbook) into the sub name ie: when to do it, how to do it, etc.
Name reference is a cell that has a fiscal year and period labeled for historical files.
Despite turning off screen updating and all that it still works like a clunky recorded macro where I sit for about 20-30 seconds while it does it's thing, and I'm just exploring to see if there's anything I can do to improve this.
Sub NewWorkbooks() 'This will make seperate workbooks for each of the tabs listed Dim wb As Workbook Dim NewBook As Workbook Dim ws As Worksheet Dim Name As String Name = ActiveWorkbook.Worksheets("Reference Tables").Range("_Name") Call TurnOffFunctions Set wb = ActiveWorkbook For Each ws In Workbooks("P&L Master Sheet"). _ Worksheets(Array("Accessories", "Leather", "Residential", "Rugs")) ws.Copy Set NewBook = ActiveWorkbook With NewBook Call break_links(NewBook) .SaveAs Filename:="C:\Users\Monthly P&L's\Historical Files\" & "P&L " & Name & " - " & ws.Name .Close 'SaveChanges:=True End With Next Call TurnOnFunctions End Sub Sub break_links(ByRef wb As Workbook) Dim Links As Variant On Error Resume Next Links = wb.LinkSources(Type:=xlLinkTypeExcelLinks) On Error GoTo 0 If Not IsEmpty(Links) Then For i = 1 To UBound(Links) wb.BreakLink _ Name:=Links(i), _ Type:=xlLinkTypeExcelLinks Next i End If End Sub Private Sub TurnOffFunctions() Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.DisplayAlerts = False Application.Calculation = xlCalculationManual Application.EnableEvents = False End Sub Private Sub TurnOnFunctions() Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.DisplayAlerts = True Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True End Sub