CODEWITHSUNDEEP
vbaexcel
William
William

Reputation: 115

Subscript Out of Range Error in Code

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

Upvotes: 0

Views: 945

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149297

This is one main reason why one should declare variables/objects and work with them :) Things like Activeworkbook/Select etc should be avoided.

You should be use the code like this

Sub Sample()
    Dim wbThis As Workbook, wbNew As Workbook
    Dim sPath As String

    sPath = "H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US&CA Benefits\Data Files\"

    Set wbThis = ThisWorkbook '<~~ "Retroactive Premiums - Semi-monthly v2.xlsm" ???

    '
    '~~> Rest of the code
    '

    Set wbNew = Workbooks.Add

    wbNew.SaveAs Filename:=sPath & wbThis.Worksheets(n).Name, FileFormat:=xlCSV, CreateBackup:=False

    '
    '~~> Rest of the code
    '
End Sub

Upvotes: 0

djikay
djikay

Reputation: 10628

Your error must be related to this part of the line that's giving you the error:

Workbooks("Retroactive Premiums - Semi-monthly v2.xlsm").Worksheets(n)

There are two reasons for this to give an error:

  1. Workbooks("Retroactive Premiums - Semi-monthly v2.xlsm"): a workbook with the specified name is not currently open.
  2. Worksheets(n): the specified workbook with that name is open but it doesn't have a sheet with the n index.

Upvotes: 1

Related Questions