Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advancedfilter doesn't work when Excel opens, but does when run macro
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel file opens and opens and opens | Excel Discussion (Misc queries) | |||
macro that opens file named in cell doesnt work | Excel Worksheet Functions | |||
Start macro when excel opens | Excel Programming | |||
Open a VBA Macro when Excel Opens | Excel Programming | |||
Runnig a macro when excel opens | Excel Programming |