Subscript Out of range VBA Error

[trinidad2011]

Hi all,
If it is possible, i would like to know what the 'Subscript out of range error means' when trying to compile a VBA code?
I changed the code that i pasted below from what i found on the internet, according to my needs.

Does anyone know what this means? When i hit the debug button it highlights this line:

​

Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into


Here is my code
Sub Consolidate()

'Summary: Prompt user to open folder
'Open file, delete unwanted data, filter latitudes
'Longitudes to fit South Australian region

Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet

'Setup
Application.ScreenUpdating = False 'speed up macro execution
Application.EnableEvents = False 'turn off other macros for now
Application.DisplayAlerts = False 'turn off system messages for now

Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into

With wsMaster
If MsgBox("Clear the o data first?", vbYesNo) = vbYes Then
.Cells.Clear
NR = 1
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
End If

'Path and filename
'Prompting user to choose the required folder

MsgBox "Please select a folder with files to consolidate"
Do
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
fPath = .SelectedItems(1) & "\"
Exit Do
Else
If MsgBox("No folder chosen, do you wish to exit Macro?", _
vbYesNo) = vbYes Then Exit Sub
End If
End With
Loop

fPathDone = fPath & "Imported\" 'remember final \ in this string
On Error Resume Next
MkDir fPathDone 'creates the completed folder if missing
On Error GoTo 0
fName = Dir(fPath & "*.csv*") 'listing of desired files


'Import a sheet from found files
Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
Set wbData = Workbooks.Open(fPath & fName) 'Open file

'This is the section to customize, replace with your own action code as needed

Columns("F:I").Select
Selection.ClearContents
Columns("A:E").Select
Selection.AutoFilter
Columns("C:C").Select 'Filter Latitude
ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"
Columns("D:D").Select 'Filter Longitude
ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
Cells.Select
Selection.Copy
Workbooks.Add

wbData.Close False 'close file
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
fName = Dir 'ready next filename
End If
Loop
End With

ErrorExit: 'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
End Sub
I will be delighted if someone can find the error or kindly explain what the error means

 

[doofusboy]

Does your workbook contained a worksheet named 'Master' (without the quote marks)?

 

[JackDanIce]

Do you have a sheet called: "Master" in the workbook you're running the macro on? That error means it can't find something it's been asked to find from the code...

 

[dribei16]

This line:

Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into

stores the Worksheet named "Master" in the wsMaster variable. The excel is complaining probably because there is no worksheet name "Master" in your file!
You need to either name a sheet "Master" or change the code.

 

[trinidad2011]

Thank you so much for the quick reply!

That bit of the code now works, in fact the entire code compiles with no run time errors.

However, for each file that it reads it opens an entirely new workbook, yet i want it it to select the filtered data from each file and paste into a common worksheet. One row after the other. That is, i need the information from each file found in the folder, imported into a single workbook. How do i do this?

Also, at the moment, there is no data being transferred, and i have checked and for most of the files chosen my conditionally ranges do include some data. I am thinking that it does not work because the section of the code which is meant to conditionally select the required data was 'recorded' and not actually coded manually.
I think that

ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
                ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004" [code\]
where it says ActiveSheet it should maybe refer to the sheet of the workbook is opening...i am unsure of what this ActiveSheet section of the code is referring to.

Could you yet again point me in the right direction, please?
Kind Regards

 

[Norie]

To stop the new workbooks getting created just remove this.

Workbooks.Add

I think you'll need to explain the rest a bit more.

It looks like you want to open a bunch of files, extract some (filtered?) data from them and copy it somewhere, the workbook the code is in perhaps?

 

[trinidad2011]

Yes. that is right.
I have a folder with approximately 200 csv. files.
Each has 9 columns, of which A:E are useful, the rest of the columns can be deleted.
COlumns C and D contain data from which in only need a specific range of values. They contain latitude and longitude, and i have min and max values that these have to satisfy. Once i have the data filtered, and i want to copy it into a brand new workbook.
for the next file in the folder i want to do the same thing, but past the filtered values in the next available row of the brand new workbook created earlier.

I got rid of the workbook.add section
I tried running it, and it now comes up with an error saying

Object Variable or With Block variable not set

when i hit debug it points to this line

 NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data

I am unsure what NR means, since i got that section from another code which i changed only slightly to incorporate my filtering.

What could be the problem?
Regards,
Trini

 

[Norie]

Trini

NR is just a variable name.

In this case NextRow would probably be more descriptive that's what it's supposed to represent - the next available row to copy into.

Try this, it's untested right now - I don't have any spare latitude/longitude CSV kicking about.

I think it should work though I'm not sure it's going to do the filtering properly - I seem to recall there some sort of SpecialCells(xlVisibleCells) needed somewhere.

Option Explicit
 
Sub Consolidate()
'Summary: Prompt user to open folder
'Open file, delete unwanted data, filter latitudes
'Longitudes to fit South Australian region
Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet
Dim wsSrc As Worksheet
 
    'Setup
Application.ScreenUpdating = False    'speed up macro execution
Application.EnableEvents = False    'turn off other macros for now
Application.DisplayAlerts = False    'turn off system messages for now
 
Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into

    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        wsMaster.Cells.Clear
        NR = 1
    Else
        NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1    ' gets next availablerow on the 'Master' Worksheet
    End If
 
    'Path and filename
    'Prompting user to choose the required folder
    MsgBox "Please select a folder with files to consolidate"

    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                fPath = .SelectedItems(1) & "\"
                Exit Do
            Else
                If MsgBox("No folder chosen, do you wish to exit Macro?", _
                          vbYesNo) = vbYes Then Exit Sub
            End If
        End With
    Loop
 
    fPathDone = fPath & "Imported\"    'remember final \ in this string
    
    On Error Resume Next
    MkDir fPathDone    'creates the completed folder if missing
    On Error GoTo 0
    
    fName = Dir(fPath & "*.csv*")    'listing of desired files

    'Import a sheet from found files
    Do While Len(fName) > 0
    
        If fName <> ThisWorkbook.Name Then    'don't reopen this file accidentally
 
            Set wbData = Workbooks.Open(fPath & fName)    'Open file
 
            Set wsSrc = wbData.Worksheets(1)

            'This is the section to customize, replace with your own action code as needed
 
            wsSrc.Columns("F:I").ClearContents

            With wsSrc
                .Columns("A:E").AutoFilter

                'Filter Latitude
                .Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
                                                   ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"

                'Filter Longitude
 

                .Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
                                                              ">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
 
                .Range("A:E").Copy wsMaster.Range("A" & NR)
                
            End With
            
            wbData.Close False    'close file

            NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1   

            Name fPath & fName As fPathDone & fName    'move file to IMPORTED folder

            fName = Dir    'ready next filename

        End If

    Loop
 
    wsMaster.Columns.AutoFit
    
    Application.DisplayAlerts = True    'turn system alerts back on
    Application.EnableEvents = True    'turn other macros back on
    Application.ScreenUpdating = True    'refreshes the screen
    
End Sub

 

[Norie]

NR is just a variable name - looks like it stands for next row and is the next available row on the master sheet.

Try this code, I'm afraid it's untested.

It think it should work, but I'm also pretty sure I'm missing something in the filtering part.

Option Explicit
 
Sub Consolidate()
'Summary: Prompt user to open folder
'Open file, delete unwanted data, filter latitudes
'Longitudes to fit South Australian region
Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet
Dim wsSrc As Worksheet
    'Setup
Application.ScreenUpdating = False    'speed up macro execution
Application.EnableEvents = False    'turn off other macros for now
Application.DisplayAlerts = False    'turn off system messages for now
 
Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into

    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        wsMaster.Cells.Clear
        NR = 1
    Else
        NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1    ' gets next availablerow on the 'Master' Worksheet
    End If
 
    'Path and filename
    'Prompting user to choose the required folder
    MsgBox "Please select a folder with files to consolidate"
    Do
        With Application.FileDialog(msoFileDialogFolderPicker)
            .AllowMultiSelect = False
            .Show
            If .SelectedItems.Count > 0 Then
                fPath = .SelectedItems(1) & "\"
                Exit Do
            Else
                If MsgBox("No folder chosen, do you wish to exit Macro?", _
                          vbYesNo) = vbYes Then Exit Sub
            End If
        End With
    Loop
 
    fPathDone = fPath & "Imported\"    'remember final \ in this string
    
    On Error Resume Next
    MkDir fPathDone    'creates the completed folder if missing
    On Error GoTo 0
    
    fName = Dir(fPath & "*.csv*")    'listing of desired files

    'Import a sheet from found files
    Do While Len(fName) > 0
    
        If fName <> ThisWorkbook.Name Then    'don't reopen this file accidentally
 
            Set wbData = Workbooks.Open(fPath & fName)    'Open file
 
            Set wsSrc = wbData.Worksheets(1) ' assume only one worksheet in CSV file

            'This is the section to customize, replace with your own action code as needed
            wsSrc.Columns("F:I").ClearContents

            With wsSrc
 
                .Columns("A:E").AutoFilter

                'Filter Latitude
                .Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
                                                   ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"

                'Filter Longitude
                .Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
                                                              ">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
 
                .Range("A:E").Copy wsMaster.Range("A" & NR)
                
            End With
            
            wbData.Close False    'close file
 
            'Next row

            NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1               Name fPath & fName As fPathDone & fName    'move file to IMPORTED folder

            fName = Dir    'ready next filename
        End If
    Loop
 
    wsMaster.Columns.AutoFit
    
    Application.DisplayAlerts = True    'turn system alerts back on
    Application.EnableEvents = True    'turn other macros back on
    Application.ScreenUpdating = True    'refreshes the screen
    
End Sub

 

[trinidad2011]

How do i thank you enough?! thank you. this macro will save me hours!
it works perfectly, i changed the filtering a bit to suit my needs, but thats all!

However, i do not yet understand why you created a 'wsSrc' ?