Copying Rows from 1 sheet to another based on a cell value

[JADownie]

Hello, I am hoping that someone might be able to give me some guidance here today.

Would something like this even be possible now with VBA, or am I dreaming when I think that this could be done....

First, on my sheet Summary delete all rows active rows on this sheet after last row in PivotTable1

Next, the same sheet Summary determine the first available empty row (skip 3 more)

Then look in cell B1 on sheet Summary, and in this blank row from above below copy over all rows from sheet AllData where B1 = same value in Column C on AllData sheet (also copy over header row 1)

 

[JADownie]

That worked and it pulled in the header row now, but it still pasted in the next row right after my last Pivot table. I was hoping it could skip 2 rows so that they would not be right on top of each other.

 

[Domenic]

Oh I see, so when pasting the data, first look for the last used row, then skip the next two rows, and then paste, correct? If so, try the following instead...

Sub CopyRows()

    Dim c As Range
    Dim LastRow As Long
    Dim NextRow As Long
    Dim Source As Worksheet
    Dim Target As Worksheet
    
    
    Set Source = ActiveWorkbook.Worksheets("AllData")
    With Source
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    Set Target = ActiveWorkbook.Worksheets("Summary")
    With Target
        NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 3
    End With
    
    Source.Rows(1).Copy Target.Rows(NextRow)
    
    NextRow = NextRow + 1
    
    For Each c In Source.Range("A1:A" & LastRow)
        If c.Value = Target.Range("B1").Value Then
            c.EntireRow.Copy Target.Rows(NextRow)
            NextRow = NextRow + 1
        End If
    Next c
    
End Sub

 

[JADownie]

YES - PERFECTION!! Thank You!!

 

[Domenic]

You're very welcome, I'm glad I could help.

Cheers!