VBA advancedfilter crashes when XLS opens but NOT when run through VBE

[jmy32]

I have a userform that takes data and creates a criteria range, output range and input range and runs an advancedfilter on it to create a subset of data. The userform is auto-loaded when the XLS is opened, do the advanced filter and then copy that subset to a new workbook, and then to close the original without saving any changes.

When I run it from scratch I get the 1004 error on the advancedfilter line of code. At that point if I End the execution and look in the data it has set up the columns for filtering correctly.

If I then go in the VB editor and run the userform it will execute the same code with no problems and give me the correct output.

What am I missing???

Not sure if this code will help without the data in the spreadsheet.

' Find the size of the dataset
finalrow = Cells(65536, 1).End(xlUp).Row
nextcol = Cells(1, 255).End(xlToLeft).Column + 5



Select Case focus
Case "APL"
Worksheets("Assets").Cells(1, nextcol + 1).Value = Worksheets("Assets").Range("N1").Value
Case "BAC"
Worksheets("Assets").Cells(1, nextcol + 1).Value = Worksheets("Assets").Range("S1").Value
End Select

' Set up the criteria range. This is based off the role AND focus.
Select Case lstRole.Value
Case "Technical Specialist"
Worksheets("Assets").Cells(1, nextcol).Value = Worksheets("Assets").Range("C1").Value
Case "Technical Consultant"
Worksheets("Assets").Cells(1, nextcol).Value = Worksheets("Assets").Range("D1").Value
Case "Solution Consultant"
Worksheets("Assets").Cells(1, nextcol).Value = Worksheets("Assets").Range("E1").Value
Case "Opportunity Manager"
Worksheets("Assets").Cells(1, nextcol).Value = Worksheets("Assets").Range("F1").Value
End Select

' Put a y in the criteria range as that is what is being searched for
Worksheets("Assets").Cells(2, nextcol).Value = "y"
Worksheets("Assets").Cells(2, nextcol + 1).Value = "y"

Set CRange = Worksheets("Assets").Cells(1, nextcol).Resize(2, 2)

Worksheets("Assets").Cells(1, nextcol + 5).Resize(1, 5).Value = _
Array(Worksheets("Assets").Cells(1, 1), Worksheets("Assets").Cells(1, 2), Worksheets("Assets").Cells(1, 22), Worksheets("Assets").Cells(1, 23), Worksheets("Assets").Cells(1, 25))
Set ORange = Worksheets("Assets").Cells(1, nextcol + 5).Resize(1, 5)

Set IRange = Worksheets("Assets").Range("A1").Resize(finalrow, nextcol - 5)

IRange.AdvancedFilter xlFilterCopy, CRange, ORange