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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,314
Office Version
  1. 365
  2. 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
21,314
Office Version
  1. 365
  2. 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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,153
Messages
5,857,676
Members
431,891
Latest member
shirazx3

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