I have a macro that moves data from a master sheet to their respective sheets in a workbook by group and then creates a separate workbook of each of those sheets... But I have been getting an error and don't remember having changed anything on it. Can someone let me know what is wrong and how to fix it?
Subscript out of range error in line starting with Activeworkbook.SaveAs...
Sub transfer_data()
Application.ScreenUpdating = False
Dim filter_criteria As String
Dim bridge_rows As Integer
Dim rng As Range
Dim rng2 As Range
Dim dest_num_rows As Integer
bridge_rows = Worksheets("Bridge").Range("A1").CurrentRegion.Rows.Count
Set rng = Worksheets("Master").Range("A6").CurrentRegion
For n = 3 To bridge_rows + 1
filter_criteria = Application.WorksheetFunction.Index(Worksheets("Bridge").Range("A1:B" & bridge_rows), Application.WorksheetFunction.Match(Worksheets(n).Name, Worksheets("Bridge").Range("B1:B" & bridge_rows), 0), 1)
dest_num_rows = Worksheets(n).Range("A1").CurrentRegion.Rows.Count
rng.AutoFilter Field:=7, Criteria1:=filter_criteria
Set rng2 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 6)
rng2.Copy Destination:=Worksheets(n).Range("A" & dest_num_rows + 1)
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US&CA Benefits\Data Files\" & Workbooks("Retroactive Premiums - Semi-monthly v2.xlsm").Worksheets(n).Name, FileFormat:=xlCSV, CreateBackup:=False
ThisWorkbook.Sheets(n).Range("A1").CurrentRegion.Copy Destination:=ActiveWorkbook.Worksheets(1).Range("A1")
ActiveWorkbook.Close savechanges:=True
Next n
rng.AutoFilter
Worksheets("Master").Range("A7:A" & rng.Rows.Count + 5).Clear
Worksheets("Master").Range("D7:D" & rng.Rows.Count + 5).Clear
Application.ScreenUpdating = True
End Sub