Filter and paste from a table to another worksheet

[Jamuson]

I am trying to paste filtered data into another worksheet. The issue I'm having is that excel will paste the entireRow and not just columns A:M into the new sheet.
My data starts at A:5 to M5 on the source sheet and I want to only paste "rows" (A5:M5) in which column G contains "PLG", skipping sequentially to the next open row in the destination worksheet.

This is the source sheet layout, and the destination sheet is identical. My source sheet has other information in adjacent cells to the right of column M. I also want to keep the destination formatting and just paste the values. I would like this to happen automatically or with a macro, any ideas?

 

[vcoolio]

Hello Jamuson,

See if the following code helps:-

Sub Test()

    Application.ScreenUpdating = False
    
        With Sheet1.Range("G4", Sheet1.Range("G" & Sheet1.Rows.Count).End(xlUp))
                .AutoFilter 1, "PLG"
                .Offset(1, -6).Resize(.Rows.Count - 1, 13).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
                .AutoFilter
        End With
    
    Application.ScreenUpdating = True

End Sub

I'm assuming that your headings are in row 4. I've used sheet codes(Sheet1 and Sheet2) as the source/destination sheets. This may have to be altered to suit.

I hope that this helps.

Cheerio,
vcoolio.

 

[Jamuson]

Not quite, this filters on sheet1 column A and pastes all values from sheet1, not just the PLG's on sheet2 and they are pasted in row 70 not starting at row 5

 

[vcoolio]

It appears then that you are using a formal Excel table. Hence, try it as follows:-

Sub Test()

    Application.ScreenUpdating = False
    
        With Sheet1.ListObjects("Table1").Range
                .AutoFilter 7, "PLG"
                .Columns("A:M").Offset(1).Resize(.Rows.Count - 1).Copy
                Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
                .AutoFilter
        End With
    
    Application.ScreenUpdating = True

End Sub

You may have to change the table name to suit.
Are the filtered values going to a formal table in sheet2 as well?

Cheerio,
vcoolio.