0

I want to copy cells from wbSource and paste them into wbTarget using VBA. The loop is so it gets pasted in the last empty column of wbTarget. Why do I get the error "Run-time error '438': Object doesn't support this property or method" at the Set wbTarget line??

Error at 

Set wsTarget = Workbooks("Data Sheet.xlsm").Worksheets("Sheet1")

Sub send_data()

Dim wbSource As Worksheet
Dim wbTarget As Worksheet
Dim i As Integer
i = 1

Workbooks.Open "H:\test\Data Sheet.xlsm"
Set wsTarget = Workbooks("Data Sheet.xlsm").Worksheets("Sheet1")
Set wsSource = ThisWorkbook.Worksheets("Sheet1")

With wsTarget
Do Until IsEmpty(Cells(1, i)) = True
    i = i + 1
Loop
End With

wsSource.Range("B1:B2").Copy
wsTarget.Cells(1, i).Paste
wsSource.Range("B13:B14").Copy
wsTarget.Cells(3, i).Paste
wsSource.Range("H16:H48").Copy
wsTarget.Cells(5, i).Paste

End Sub
Jonathan Williams
  • 1
  • 1
  • Side note, see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for a better way to find the last row. – BigBen Apr 17 '20 at 20:22
  • A workbook doesn't have `Cells`... a worksheet does. – BigBen Apr 17 '20 at 20:22
  • @BigBen I get the same error at the same line with this code ``` Sub send_data() Dim wsSource As Worksheet Dim wsTarget As Worksheet Dim i As Integer i = 1 Workbooks.Open "H:\test\Data Sheet.xlsm" Set wsTarget = Workbooks("Data Sheet.xlsm").Worksheets("Sheet1") Set wsSource = ThisWorkbook.Worksheets("Sheet1") ``` – Jonathan Williams Apr 17 '20 at 20:30
  • `Worksheets` - you're missing an `s`. – BigBen Apr 17 '20 at 20:35
  • I apologize, same error though. Thanks for your help BTW – Jonathan Williams Apr 17 '20 at 20:37
  • 1
    Add `Dim wb as Workbook`, then change `Workbooks.Open "H:\test\Data Sheet.xlsm"` to `Set wb = Workbooks.Open("H:\test\Data Sheet.xlsm")`, then change the problematic line to `Set wsTarget = wb.Worksheets("Sheet1")`. – BigBen Apr 17 '20 at 20:39
  • 1
    In your `With` block you're missing the `.` in front of `Cells(1, i)` – Tim Williams Apr 17 '20 at 21:08

0 Answers0