Macro code to autofilter & select dynamic data.

[Yup]

Hi, I have to copy rows from one sheet to other in a same excel file. It's a repetitive task. Consider following example.

Name	Devices	code
A	Desktop	234
B	Laptop	2345
C	Laptop	3456
D	Smartphone	7654
E	Printer	3456
F	Desktop	098
G	Scanner	123
H	Laptop	65
I	Smartphone	876
J	Laptop	345

I want to filter "Devices". Unselect "Select All", Select "Printer" & "Scanner". copy the rows. paste it in another sheet.
But sometimes "Scanner" may not even be present in "Devices" column. In that scenario, I want to copy rows containing "Printer" & paste.
I need a same code that will work in both scenarios.

 

[kevin9999]

The following assumes your first row is the header row and that your actual data starts in cell A2. Change the sheet names & destination to suit.

Option Explicit
Sub Copy_Array()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")  '<~~ change to suit
    Set ws2 = Worksheets("Sheet2")  '<~~ change to suit
    
    With ws1.Range("A1").CurrentRegion
        .AutoFilter 2, Array("Printer", "Scanner"), 7
        If ws1.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).Copy ws2.Range("A2") '<~~ change destination to suit
        End If
        .AutoFilter
    End With

End Sub

 

[Fluff]

Duplicate to: Need a Macro code to filter & unselect a specific value.

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.