VBA Run-Time Error Caused by Power Query?

[phelbin]

I'm using the following code to copy paste from one workbook to another, and it gets hung up on the line in bold -- with Run-Time error 1004. I use this code on a half dozen workbooks, but this is the only one where it doesn't work.


In this workbook, I use Power Query to pull data from a SQL database into this workbook, and that seems to be the problem. I copied the data to a new workbook, and the code worked great. But when I pulled the Power Query back into the mix, sure enough...run-time error 1004. So I'm pretty sure that's the issue.

    Dim ws As Worksheet, ws2 As Worksheet, x As Range, i As Long, y As Long
    Set ws = Workbooks("1516 BUDGET.xlsm").Sheets("Sheet1")
    Set ws2 = Workbooks("Purchases Upload.xls").Sheets("Import")
    
    With ws
        For i = 1 To .UsedRange.Columns.Count
        Set x = ws2.Rows(2).Find(ws.Cells(1, i).Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not x Is Nothing Then
                y = .Cells(Rows.Count, i).End(3).Row
                .Range(.Cells(2, i), .Cells(y, i)).Copy ws2.Cells(3, x.Column)
            End If
        Set x = Nothing
        Next i
    End With

This is the last step in a long macro, and everything before it works just fine. What is it about this code set that doesn't want to coexist with Power Query?


Thanks!