CODEWITHSUNDEEP
vbaexcelfilter
Stupid.Fat.Cat
Stupid.Fat.Cat

Reputation: 11285

VBA Excel How to maintain filters when copying a row

So I got a row with associated Filters on them (there's an arrow on each of these cells and when you click on them, the filter box appears which allows the user to filter specific content). But I'm having a slight issue, this is the code I use for copying one row from one sheet to another:

Sheets(Name).Range("A1").EntireRow.Copy Destination:= _
     Sheets("Monthly").Range("A1").End(xlUp).Offset(Sheets("Monthly").UsedRange.rows.Count - 1)
Cells.EntireColumn.AutoFit

The problem is, when this code copies the row, they lose their filter properties. Is there a way to keep this information?

Upvotes: 0

Views: 160

Answers (2)

Brock Gion
Brock Gion

Reputation: 956

By default the Range.AutoFilter method will apply to the entire row, so you can just put the first cell of the row like this...

Worksheets("Monthly").Range("A10").AutoFilter

Also, for more control, you can pass additional arguments:

Worksheets("Monthly").Range("A10").AutoFilter _
    field:=1, _
    Criteria1:="nameToMatch", _
    VisibleDropDown:=True

In Excel VBE (Visual Basic Editor), just highlight anything you want to find more information about and hit F1 to bring up help.

The Excel Developer Reference can be very helpful.

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

As follow up from comments:

Autofilter should be appied explicitly on sheet Monthly. Say, if you copy row №1 from sheet1 (with autofilter) to sheet Monthly in, say, row №10, then you should use this line to apply autofilter:

Sheets("Monthly").Range("A10:Z10").AutoFilter

change row number and columns to suit.

Upvotes: 1

Related Questions