CODEWITHSUNDEEP
vbaexcelfilter
lydias
lydias

Reputation: 841

VBA Filter stuck on second row

I'm filtering on multiple sheets by using the following code:

The scenario is I'm copying data from one workbook and paste them on to the current workbook. Sheets (Table 1 to Table 13 S) pull data from the sheet with copied data through formulas. I have used the same code for other workbook with the same structured data, the only difference is that I copy different rows.

For some strange reason, in this workbook, from Table 1 to Table 13, the filers are stuck on the second row. However, for Table 1 S to Table 13 S the filters are all normal.

I'm pulling my hair out for this lol...

Set y = ActiveWorkbook

y.Sheets("Table 1").Range("AA2").AutoFilter Field:=27, Criteria1:="x"
y.Sheets("Table 2").Range("AB2").AutoFilter Field:=28, Criteria1:="x"
y.Sheets("Table 3").Range("AC2").AutoFilter Field:=29, Criteria1:="x"
y.Sheets("Table 4").Range("AD2").AutoFilter Field:=30, Criteria1:="x"
y.Sheets("Table 8").Range("AE2").AutoFilter Field:=31, Criteria1:="x"
y.Sheets("Table 11").Range("AF2").AutoFilter Field:=32, Criteria1:="x"
y.Sheets("Table 12").Range("AG2").AutoFilter Field:=33, Criteria1:="x"
y.Sheets("Table 13").Range("AH2").AutoFilter Field:=34, Criteria1:="x"

y.Sheets("Table 1 S").Range("AA2").AutoFilter Field:=27, Criteria1:="x"
y.Sheets("Table 2 S").Range("AB2").AutoFilter Field:=28, Criteria1:="x"
y.Sheets("Table 3 S").Range("AC2").AutoFilter Field:=29, Criteria1:="x"
y.Sheets("Table 4 S").Range("AD2").AutoFilter Field:=30, Criteria1:="x"
y.Sheets("Table 8 S").Range("AE2").AutoFilter Field:=31, Criteria1:="x"
y.Sheets("Table 11 S").Range("AF2").AutoFilter Field:=32, Criteria1:="x"
y.Sheets("Table 12 S").Range("AG2").AutoFilter Field:=33, Criteria1:="x"
y.Sheets("Table 13 S").Range("AH2").AutoFilter Field:=34, Criteria1:="x"

Upvotes: 1

Views: 584

Answers (1)

Jarom
Jarom

Reputation: 1077

I wasn't able to replicate the exact problem you are having. There is probably something different between the worksheets that makes Excel guess the filtered range incorrectly. I don't know what the root cause of the problem is, but I think telling excel the explicit range to put the filter on will fix the problem. Otherwise excel is guessing where you want the range.

For example, if the range of the data (including headers) for Table 1 is AA1 to AF500 then I would make the code look like this:

y.Sheets("Table 1").Range("AA1:AF500").AutoFilter Field:=27, Criteria1:="x"

If the range is dynamic VBA has a lot of different ways to handle that. So you can explicitly specify a dynamic range using one of those techniques.

Upvotes: 2

Related Questions