VBA Error

newbie18

New Member
Joined
Jul 30, 2018
Messages
12
I am getting a subscript out of range error on my VBA below (Set wsRawData = ThisWorkbook.Worksheets("Raw Data")) and can't figure out how to fix it. My worksheet is named Raw Data. Would anyone be able to advise on how to fix this? Thanks!


Option Explicit

Dim wsRawData As Worksheet
Dim LastRow As Long
Dim collection_UniqueList As Collection

Public Const Output_Folder_Path As String = "<Output directory>"

Sub MainProgram()

Dim instance As Long
Dim wb As Workbook

Set wsRawData = ThisWorkbook.Worksheets("Raw Data")

With wsRawData

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row

If 2 > LastRow Then Exit Sub

Set collection_UniqueList = New Collection

Call UniqueList(collection_UniqueList)

For instance = 1 To collection_UniqueList.Count

.AutoFilterMode = False

.Range("A1:AN" & LastRow).AutoFilter .Range("AN1").Column, collection_UniqueList.Item(instance)

.Range("A1").CurrentRegion.Copy

Set wb = Workbooks.Add

wb.Worksheets(1).Paste

wb.SaveAs Filename:=Output_Folder_Path & "_" & Replace(Replace(collection_UniqueList.Item(instance), "/", ""), Chr(34), "") & ".xlsx"
wb.Close False

Set wb = Nothing

.AutoFilterMode = False

Next instance

End With

MsgBox "Macro complete.", vbInformation

Set collection_UniqueList = Nothing
Set wsRawData = Nothing

End Sub

Private Sub UniqueList(ByRef col As Collection)

Dim RowNumber As Long

With wsRawData

On Error Resume Next
For RowNumber = 2 To LastRow
collection_UniqueList.Add .Cells(RowNumber, "AN").Value, CStr(.Cells(RowNumber, "AN").Value)
Next RowNumber

End With

End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,113
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Recheck your Raw Data spelling on the sheet Tab, looking especially for leading / trailling / double spaces !!
The line works fine for me
 

newbie18

New Member
Joined
Jul 30, 2018
Messages
12
Recheck your Raw Data spelling on the sheet Tab, looking especially for leading / trailling / double spaces !!
The line works fine for me
I have checked for all of those possibilities even retyping the worksheet name, and it still didn't fix the error. Should I resave the file and redo the VBA?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,113
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I don't have Excel at the moment, but I think this is an illegal statement
VBA Code:
Public Const Output_Folder_Path As String = "<Output directory>"
and might be throwing your error
 

newbie18

New Member
Joined
Jul 30, 2018
Messages
12
I don't have Excel at the moment, but I think this is an illegal statement
VBA Code:
Public Const Output_Folder_Path As String = "<Output directory>"
and might be throwing your error
i changed it to have the correct path.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,113
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Did that work ??
 

Watch MrExcel Video

Forum statistics

Threads
1,129,378
Messages
5,635,903
Members
416,887
Latest member
SheriE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top