We help IT Professionals succeed at work.

I need an Excel Macro modifed to filter out "R2O" and "Asset Verification" then copy the results to a new sheet.

135 Views
Last Modified: 2017-03-07
I need this macro modified to filter out "R2O" and "Asset Verification" then copy the results to a new sheet.

Sub CopyFilteredRow()
Dim SrcWs As Worksheet, NewSh As Worksheet
Dim RngFilter As Range
Dim SrcLR As Long
With Application
    .ScreenUpdating = False
    .DisplayStatusBar = True
    .StatusBar = "!!! Please Be Patient...Updating Records !!!"
    .EnableEvents = False
    .Calculation = xlManual
End With

Set SrcWs = ActiveSheet
SrcLR = SrcWs.Range("A" & Rows.Count).End(xlUp).Row
Set RngFilter = SrcWs.Range("A1:K" & SrcLR)
With RngFilter
    .AutoFilter Field:=8, Criteria1:="<>*Project*"
    .SpecialCells(xlCellTypeVisible).Copy
    Worksheets.Add.Paste
End With
ActiveSheet.Name = "FilteredSheet"
Set NewSh = Worksheets("FilteredSheet")
Application.CutCopyMode = False
If SrcWs.AutoFilterMode = True Then SrcWs.AutoFilterMode = False
NewSh.Activate
NewSh.Columns.AutoFit
NewSh.Range("A2").Select
ActiveWindow.FreezePanes = True
With Application
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .StatusBar = False
    .EnableEvents = True
    .Calculation = xlAutomatic
End With
End Sub

Open in new window

Comment
Watch Question

Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
Just like with the current Macro, which removes rows with Project in column H, I also need the other 2 items removed then the data copied over to a new sheet.
Managing Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Edward PamiasTeam Lead RRS Desk
Top Expert 2016

Author

Commented:
@shums, I had to modify this, because I did not save my original report that I pull. So I created a new one and was able to get it working with the new report. I have another question coming to add to this Macro. :)
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
You're Welcome Edward! Glad it worked with new criteria :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.