Macro to select only criteria with AutoFiltered Selection

aka_krakur

Active Member
Joined
Jan 31, 2006
Messages
438
Can someone help me with the VB for a macro to select only the rows/cells within an autofiltered selection. So if I Autofiltered this set of data by Column F where Lookup = NO
it would only display Rows 3 & Row 7.
I would want the Macro then to select only these rows, then copy/paste them to another Worksheet called "Static".
Book2
ABCDEF
1Incident#EntryDateReceivedDate#ofdays,EntrytoReceivedInstAct#Lookup
2123456-102/16/0902/24/098A1YES
3654321-202/17/0902/19/092A2NO
4456125-102/17/0902/25/098A3YES
5345678-302/17/0902/25/098A4YES
6123456-202/17/0902/23/096A1YES
7654321-302/17/0902/23/096A2NO
8456125-202/17/0902/23/096A3YES
9345678-402/17/0902/23/096A4YES
10123456-302/17/0902/25/098A1YES
Sheet1
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
aka_krakur,

Before the macro:

Excel Workbook
ABCDEF
1Incident*#Entry*DateReceived*Date#*of*days,*Entry*to*ReceivedInst*Act*#Lookup
2123456-12/16/20092/24/20098A1YES
3654321-22/17/20092/19/20092A2NO
4456125-12/17/20092/25/20098A3YES
5345678-32/17/20092/25/20098A4YES
6123456-22/17/20092/23/20096A1YES
7654321-32/17/20092/23/20096A2NO
8456125-22/17/20092/23/20096A3YES
9345678-42/17/20092/23/20096A4YES
10123456-32/17/20092/25/20098A1YES
Sheet1



Excel Workbook
ABCDEF
1Incident*#Entry*DateReceived*Date#*of*days,*Entry*to*ReceivedInst*Act*#Lookup
2
3
4
Static



After the macro:


Excel Workbook
ABCDEF
1Incident*#Entry*DateReceived*Date#*of*days,*Entry*to*ReceivedInst*Act*#Lookup
2654321-22/17/20092/19/20092A2NO
3654321-32/17/20092/23/20096A2NO
4
Static



Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below code, and paste it into the Module (on the right pane).


Code:
Option Explicit
Sub Test()
    Dim LR As Long, SLR As Long
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        LR = .Cells(Rows.Count, 1).End(xlUp).Row
        SLR = Sheets("Static").Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A1:F" & LR)
            .AutoFilter Field:=6, Criteria1:="NO"
            .Offset(1).Resize(LR).EntireRow.Copy Sheets("Static").Range("A" & SLR + 1)
            .AutoFilter
        End With
    End With
End Sub


Then run the "Test" macro.


Have a great day,
Stan
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top