VBA Copy Paste Filtered Data from one Workbook to another

Question

i am stuck on the last part of the code below. Basically i need to filter my "wb" data from one workbook then paste it to a tab in a new workbook that i created. I am able to do everything except copy the filtered data into the new workbook tab. Any help would be appreciated. Also the data is dynamic, which is why i used "UsedRange". SaveAs paths have been edited for privacy.

Sub Save()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks.Add
ThisWorkbook.Sheets("Due_Date_Approaching").Copy Before:=wb.Sheets(1)
ThisWorkbook.Sheets("Overdue").Copy Before:=wb.Sheets(1)
wb.Sheets(2).Name = "Due Date Approaching"
 Application.DisplayAlerts = False
wb.Sheets("Sheet1").Delete

wb.SaveAs "C:test1.xlsx"

Worksheets("Overdue").Activate
Worksheets("Overdue").Range("A1").AutoFilter Field:=35, Criteria1:="ASIA"
Worksheets("Overdue").UsedRange.Copy

Dim APAC As Workbook
Set APAC = Workbooks.Add
ActiveWorkbook.Worksheets.Add Count:=2
APAC.Sheets(1).Name = "Overdue"
APAC.Sheets(2).Name = "Due Date Approaching"
Application.DisplayAlerts = False
APAC.Sheets("Sheet1").Delete

APAC.SaveAs "C:Test2.xlsx"

'This is where it gives me "Object doesnt support this property or method"
Workbooks("Test2.xlsx").Worksheets("Overdue").Range("A1").Paste

Application.CutCopyMode = False


End Sub

Does this answer your question? [Copy filtered data to another sheet using VBA](https://stackoverflow.com/questions/39121960/copy-filtered-data-to-another-sheet-using-vba) — niton, Sep 22 '21 at 19:00
I think [THIS](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) does exactly what you want? — Siddharth Rout, Sep 22 '21 at 19:12
They do not i did a search before posting an am still getting an error at the point wher ei have to paste the data. I either get the "object doesnt support this property or methons" or "Paste method of worksheet class failed" — Josh Hudson, Sep 22 '21 at 19:14
@Josh Called by what ? You have the reference to it in APAC e.g. `APAC.Worksheets("Overdue").Range("A1").Paste`. — CDP1802, Sep 23 '21 at 14:54

Darrell H · Accepted Answer · 2021-09-22T19:51:11.670

It could be prettier, but functionally I think you missed the point to the links above. You most likely only want to copy the visible cells from your filter and you need not clear the clipboard before pasting. Using CurrentRegion as I did here assumes you are using continuous data. If not, it is better to declare the range using the last row and last column. Here is a revision:

Sub Save()

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
    Dim wb As Workbook
    Set wb = Workbooks.Add
    ThisWorkbook.Sheets("Due_Date_Approaching").Copy Before:=wb.Sheets(1)
    ThisWorkbook.Sheets("Overdue").Copy Before:=wb.Sheets(1)
    wb.Sheets(2).Name = "Due Date Approaching"
    wb.Sheets("Sheet1").Delete
    
    wb.SaveAs "C:test1.xlsx"
    
    Dim APAC As Workbook
    Set APAC = Workbooks.Add
    ActiveWorkbook.Worksheets.Add Count:=2
    APAC.Sheets(1).Name = "Overdue"
    APAC.Sheets(2).Name = "Due Date Approaching"
    APAC.Sheets("Sheet1").Delete
    
    APAC.SaveAs "C:Test2.xlsx"
    
    ThisWorkbook.Worksheets("Overdue").Range("A1").CurrentRegion.AutoFilter Field:=35, Criteria1:="ASIA"
    ThisWorkbook.Worksheets("Overdue").Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
    Workbooks("Test2.xlsx").Worksheets("Overdue").Range("A1").PasteSpecial xlPasteAll
    
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

Thanks this is definitely much neater, but it still doesnt work or me. I get the Subscript out of range error. — Josh Hudson, Sep 23 '21 at 11:40
If that error occurs, it means that something doesn't exist. If you look at the line where it occurs, you just need to check the spelling or make sure it is in the workbook you designate. Which line does it occur on? — Darrell H, Sep 23 '21 at 14:06
it happens on the paste line every time. I gave up and made sheets in the existing workbook and am just exporting them to their own after it does all the work — Josh Hudson, Sep 23 '21 at 19:55
You did mention you hid the saveas. Try it without .xlsx at the end. If it is open that isn’t needed. And check the saveas that you did in fact call it `Test2` — Darrell H, Sep 23 '21 at 22:33

VBA Copy Paste Filtered Data from one Workbook to another

1 Answers1