Copy Table from worksheet1 to worksheeet2 using Autofilters

satmisha used Ask the Experts™
Hi experts,
I want to copy table 'Table1'  from sheet1 to sheet2 when user clicks on button , with one condition i.e. only those rows where cell value is not blank.

Enclosing sample file fyr.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hope you can tweak this to fit your needs.

Sub Demo()
    Sheets("Sheet1").Range("A1:D" & Cells(Rows.Count, 1).End(xlUp).Row).AutoFilter 2, <>""
    Sheets("Sheet1").Range("A1:D" & Cells(Rows.Count, 1).End(xlUp).Row).Copy Sheets("Sheet2").Range("A65000").End(xlUp).Offset(1, 0)
    Sheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=2
End Sub

Open in new window


Thanks for your reply, Apologies for my late reply...

Would it be possible to copy autofiltered range to array ? As after filtering data I need to copy filtered rows to destination table at alternative rows... for example

if we have 5 filetered rows then I need to copy to destination table at row1, row3, row5, row7 and row9.

Looking forward to hearing from you.
That doesn't look like part of the original question. Please ask a new question.


THnks.. It does work

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial