I am trying to loop through code that copies an autofiltered range from one workbook and paste it into another. I

Question

I have a loop that is taking a dynamic range in workbook A and pasting it into workbook B, cell D4. Then it loops through again and pastes another dynamic range in workbook C, cell D4. But the two ranges may be different sizes.
Im getting a "PasteSpecial method of Range class failed"

Ive tried all three methods of copy/paste seen here https://www.excelcampus.com/vba/copy-paste-cells-vba-macros/ The first two examples don't copy and the last one copies but gives me the PasteSpecial error above.

Sub CopyPaste()
  Dim AX As Workbook
  Dim WB As Workbook
  Dim rng As Range
  Dim autofiltrng As Range
  Dim xlApp As Excel.Application

  Set xlApp = CreateObject("Excel.Application")
  Set AX = Workbooks.Open("C:\Desktop\AXFile.xlsx")
  Set WB = Workbooks.Open("C:\Desktop\ClientA.xlsx")

  'filtering AX file to show 1 specific client
  With Axys
    AX.Sheets(1).Range("A2").AutoFilter Field:=4, Criteria1:="ClientA"
  End With

  With ActiveSheet.AutoFilter.Range
    On Error Resume Next                        
  Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count - 1,_ 
    1).SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
  End With

  Set rng = AX.Sheets(1).AutoFilter.Range
  'copy/paste data for specific client in their own XLS
  rng.Offset(1, 5).Resize(rng.Rows.Count - 5).Copy
  WB.Sheets(1).Range("a14").PasteSpecial xlPasteValues
  Application.CutCopyMode = False

WB.Save
WB.Close
AX.Close

End Sub

I took the loop out of the above code, but every time I loop through the 50+ clients to create 50+ EXCEL files I get the PasteSpecial error at some random point in the process. Never the same place.

Hi and welcome! I'm a bit confused about what autofiltrng does. You seem to set it and never use it. It's also almost never a good idea to use On Error GoTo 0, it's better to check for errors before they happen. Is it possible that there are fewer than 5 matches when you filter your range? — seadoggie01
I can see a few things in your code that could potentially throw an error. As a first step, you should go through your code in debugging mode so you can narrow down the line that is causing the error. I am going to put some things down in a new comment that look a bit off to me... — dra_red
1) 'With axys' is used but not required (that is not going to produce the error though.2) You are referring to activesheet rather than the specific sheet. Sticking 'autofilter' on won't necessarily make that sheet the 'activesheet'. Why add that ambiguity? 3) THIS is probably the issue....you are using offset for rows AND columns (ie 5 columns over) but then subtracting 'rows'. It smells of an error...I would check that out — dra_red

SmileyFtW SmileyFtW · Accepted Answer · 2019-10-25T00:37:45

Suggest you capture the info you are interested in explicitly rather than using a filter and copy/paste. Then load that info directly into a target.

There is no need to create an xlApp that you never use and then never dispose of (Set xlApp=Nothing).

I am trying to loop through code that copies an autofiltered range from one workbook and paste it into another. I

2 Answers