Not sure what title to put on this, because the macro I was using was working just fine, until all of a sudden it wasn't. Go figure. Basically I have a workbook with several pages of spreadsheets. The first page is titled "Reporting", the rest are titled by Month and Year ("August 2013"). I also have a userform to do some reporting on these months. The form is two list boxes, one with a list of available worksheets minus the Reporting sheet, the other with a list of potential filter criteria. The intent is to select a sheet, then use the second listbox to filter that sheet by the selected criteria. Worked fine, until all of a sudden it didn't. Now I get "Compile Error: Can't find project or library". When I debug it highlights the first "N" on the first line of code "For N = 0..." Can anyone tell me what is wrong here? Note that I use a lot of 's to take notes on my code so I know what everything does.
Code:
Private Sub CommandButton1_Click()
''''''''''''sheet select
'gets listbox2 item count
For N = 0 To ListBox2.ListCount - 1
If ListBox2.Selected(N) = True Then
'msgbox to confirm selected item, error checking
MsgBox (ListBox2.List(N))
Sheets(ListBox2.List(N)).Select
''''''''''''filter criteria
'gets listbox1 item count
For i = 0 To ListBox1.ListCount - 1
'selects filter range
Range("A3:M3").Select
Range(Selection, Selection.End(xlDown)).Select
'if/then to find only selected items in listbox 1
If ListBox1.Selected(i) = True Then
'msgbox to confirm selected item, error checking
MsgBox (ListBox1.List(i))
'apply autofilter, use current selected item as criteria
Selection.autofilter Field:=5, Criteria1:=Array(ListBox1.List(i))
'copy/paste, then turn off autofilter
Sheets(ListBox2.List(N)).Select
Range("A4:M5000").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Reporting").Select
Range("A6").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
'for some reason code does not work if I leave autofilter turned on!
Sheets(ListBox2.List(N)).Select
Selection.autofilter
'end for next (i) loop
End If
'move on to next item in listbox, or next sheet if at end
Next i
'end for next (n) loop
End If
'move on to next sheet, or return to reports tab if at end
Next N
Sheets("Reporting").Select
Range("A8").Select
Unload Me
End Sub
