VBA If value exists in column R get data from subsequent columns and rows

[jacob1234]

Hi All,

I'm having some difficulty building some reports,

I have a main database where users are submitting information from a userform to the next empty row on a sheet, what I'd like to do is if a user clicks the button that it finds all values with "Pending" in Column R and get all other columns to the right (R to X) and put those on another sheet?

I'd like this macro to find all rows that have the word "Pending" in R - could someone please assist,

Thanks!!

 

[vcoolio]

Hello Jacob,

Try the following code in a standard module and assigned to a button:-

Sub TransferJacobsData()

Application.ScreenUpdating = False

With Sheet1.Range("R1", Sheet1.Range("R" & Sheet1.Rows.Count).End(xlUp))
       .AutoFilter 1, "Pending"
       Range("R2", Range("X" & Rows.Count).End(xlUp)).Copy
       Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
       .AutoFilter
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

Test it in a copy of your workbook first.
If you need the "used" data cleared from the source sheet, let me know.

I hope that this helps.

Cheerio,
vcoolio.

 

[My Aswer Is This]

Try This:
Searches sheet named "Sheet1" Column R for "Pending"
Will copy rows to sheet named "Sheet2"

Sub Auto_Filter_Me()
Application.ScreenUpdating = False
    Sheets("Sheet1").Activate
    Lastrow = Sheets("Sheet1").Cells(Rows.Count, "R").End(xlUp).Row
    With Worksheets("Sheet1").Range("R1:X" & Lastrow)
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="Pending"
        .SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet2").Range("A1")
    End With
ActiveSheet.AutoFilterMode = False
Application.ScreenUpdating = True
End Sub

 

[jacob1234]

Perfect! Thanks all

 

[vcoolio]

You're welcome Jacob. Cheers!

 

[My Aswer Is This]

Glad we were able to help you. Come back here to Mr. Excel next time you need additional assistance.

Perfect! Thanks all