VBA with filters

  • Thread starter Thread starter David
  • Start date Start date
D

David

Is it possible to have a piece of VBA code that enters a
value in a cell based on the result of filtering a column
using a auto filter, attached to a button.

So for example, a table with a column that filters A, B
and C.

so whenever 'A' is filtered (Criteria1:="A"), then enter
the value of sheet1!A1 into sheet2!A1 (for example).
Filtering A can be attached to a button to execute the
code. (as opposed to selecting A in the auto filter, this
can be executed with the following code.

Sheets("sheet1").Select
ActiveSheet.AutoFilterMode = False
Range("A7").AutoFilter Field:=7, Criteria1:="A"


I want to add to this code a condition that when it is
executed then enter the value of sheet1!A1 into sheet2!A2.

Any ideas? This will make my spreadsheet alot more
functional.

Thanks.

Regards
 
David,

When you execute an auotfilter, you don't actually hide
the data or remove it from the sheet. Therefore, cell A2
would still contain the same data that was there before
the filter was executed.

To achieve what you require can be done, and the exact
method depends on whether or not you can sort the data in
the master sheet. For example, you may have in column A
rows containing identifiers AA, BB, CC and DD. You can
write code to:
Sort by column A (Group the data)
Start at cell A2, and if the identifier equals AA, note
the ADDRESS.
move down one row, and check the current identifier, and
if it is still AA loop until it is not (DO UNTIL...LOOP)
move back one row
highlight the rows between the previously noted and
current (Range(start_Row &":" &
Activecell.address).entirerow.select)
Copy the rows
and paste to the other worksheet
Move to next criteria

and so on.

Steve
 
Back
Top