VBA code to duplicate select info

[Ashe77]

Hello all

I wonder if anyone could help me with some VBA please, I have a worksheet (named ProjectTimeline) that has various criteria in a table (columns A to P) that feeds some pivottables which then feeds a line chart. I'd like to be able to use a macro to copy the whole worksheet to a new sheet BUT exclude any entries that have a "no" in column Q.

Is this possible or am I extravagantly dreaming?

 

[igold]

if you know how to copy a sheet to a new worksheet, why don't you don't you flip flop your logic. Copy the entire sheet, and then on the new sheet delete any rows where Column Q = "no".

 

[igold]

Does this work:

Sub NoNos()


    Dim ws As Worksheet: Set ws = Worksheets("ProjectTimeline")
    Dim lRow As Long, i As Long
    
    Application.ScreenUpdating = False
    ws.Copy after:=Worksheets(ThisWorkbook.Worksheets.Count)
    lRow = Cells(Rows.Count, "Q").End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 17) = "no" Then Cells(i, 17).EntireRow.Delete
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[Ashe77]

Sir the code was PERFECT, thank you so much.

Does this work:

Sub NoNos()


    Dim ws As Worksheet: Set ws = Worksheets("ProjectTimeline")
    Dim lRow As Long, i As Long
    
    Application.ScreenUpdating = False
    ws.Copy after:=Worksheets(ThisWorkbook.Worksheets.Count)
    lRow = Cells(Rows.Count, "Q").End(xlUp).Row
    For i = lRow To 2 Step -1
        If Cells(i, 17) = "no" Then Cells(i, 17).EntireRow.Delete
    Next
    Application.ScreenUpdating = True
    
End Sub

 

[igold]

I was happy to help. Thanks for the feedback!