Autofilter - Filtering data and copying into a specific row, then changing a filter and copying into a row below the first set

Question

I am creating an Excel workbook that will use VBA to apply two filters to a sheet of requested items. Column Q contains either 'Hardware' or 'Smartphone', Column R contains either 'Yes' or 'No'. The contents of the other fields don't matter. A filter will be applied to Column Q for 'Hardware' and Column R for 'No'. For each item which meets both criteria, they will be pasted into a specific row, A11, on another tab which contains a header row in A10. Below the rows that have been pasted in, I'd like two blank rows, then a line of text "Your Text Here", and the header row will be repeated. Lastly the Column R filter will be switched to 'Yes' with the Column Q filter staying on 'Hardware'. The rows produced by this filter will be copied in below this second header row.

The Hardware tab will then contain the following,

A1-A9 - Blank, the contents will be added later.
A10 - Header row for the data,
A11-A(X) - Data rows with Column Q = "Hardware" and Column R = "No",
A(X+1)-A(X+2) - Blank rows,
A(X+3) - "Your Text Here",
A(X+4) - Header row for the data,
A(X+5) - Data rows with Column Q = "Hardware" and Column R = "Yes".

I have adapted a VBA script which I found here on this site, many thanks to Siddharth Rout. How to copy a line in excel using a specific word and pasting to another excel sheet?

When I run the macro, it applies the two filters correctly and copies the correct rows into the 'Hardware' tab. However I want it to be pasted into Cell A11 below the header row, but the data is actually overwriting this and copying into Cell A10.

Because I can't stop overwriting my header row or get the first set in the right place, I'm not sure how to then add what I need below it as well as how to determine where the second set will go.

I have tried changing the value in 'After:=.Range("A11"), _' but I am quite possibly misunderstanding what it does. I want it to always go in starting at row A11 and not touch anything in rows 10 and above even if they are blank.

    Dim wb1 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim copyFrom As Range
    Dim lRow As Long

    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Worksheets("Raw")

   With ws1

        .AutoFilterMode = False

        lRow = .Range("Q" & .Rows.Count).End(xlUp).Row

        With .Range("Q1:R" & lRow)
            .AutoFilter Field:=1, Criteria1:="Hardware" '-> Hardware Items
            .AutoFilter Field:=2, Criteria1:="No" '-> Not Business Continuity
            Set copyFrom = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With

        .AutoFilterMode = False
    End With

    '-> Destination
    Set ws2 = wb1.Worksheets("Hardware")

    With ws2
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lRow = .Cells.Find(What:="*", _
                          After:=.Range("A11"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lRow = 1
        End If

        copyFrom.Copy .Rows(lRow)
    End With

    wb1.Save
End Sub

Many thanks to anyone who may respond and apologies if my summary could be better.

Ahmad Riaz · Accepted Answer · 2020-04-28T03:50:21.233

0

If you know that you have to paste the range in A11, you can directly paste it there. Instead of using find for lrow ,inside the With ws2 do

With ws2
        copyFrom.Copy .Range("A11")
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row

This will paste the range in row A11 then you can get the next last row . Get X by (define it as Dim lrow as Long).

Now you have the lastrow that contains data (X). As you stated the next data row is X + 5 . First clear the initial filter by

ActiveSheet.AutoFilter.ShowAllData

So adjust the code to do the other filter with criteria "Yes" and then paste it by

 With ws2
         copyFrom.Copy .Range("A" & lrow + 5)

edited Apr 28 '20 at 03:50

answered Apr 28 '20 at 03:43

Ahmad Riaz

77
7

1

Ah! Brilliant, thanks! I now have the first data set being copied into the correct place, the second set being added 5 rows below the final line, and some additional lines in the script to add a header row and some text into one of the cells. – Libra Apr 28 '20 at 13:03
Good to hear! Good luck on your project. – Ahmad Riaz Apr 28 '20 at 14:31

Autofilter - Filtering data and copying into a specific row, then changing a filter and copying into a row below the first set

1 Answers1