Getting "Method 'Range' of object '_Worksheet failed" error when trying to paste row into dataset

user_10024384 0
Viewed 27

I'm trying to paste a row from my array into the corresponding row (using match) of my dataset

"

leaseid = Sheet3.Cells(1, 1).CurrentRegion
dtarray = Sheet3.Cells(1, 15).CurrentRegion
Dim ws As Worksheet
Set ws = ActiveWorkbook.Sheets("Leases")
Dim txtid As String

For a = 2 To UBound(dtarray)
        
    txtid = leaseid(a, 4)
    rowchange = Application.WorksheetFunction.Match(txtid, ws.Range("C:C"), 0)
    ws.Range(Cells(rowchange, 8), Cells(rowchange, 67)) = Application.WorksheetFunction.Index(dtarray, a, 0)
    MsgBox (rowchange)
    
Next

Basically, the code iterates thru the txtid's; rowchange finds the corresponding row from the ID and then I want to paste in the entire row (60 columns) into the data. Weirdly enough, I was able to get my code to run once and it worked ok. But now when I try to re-run the code, I'm getting the error in my title: "Method 'Range' of object '_Worksheet failed." Appreciate any help. Thank you!

vba excel
1 Answers

If ws is not active then your code will fail because the Cells() defaults to the activesheet, so you need to specify a worksheet there:

ws.Range(ws.Cells(rowchange, 8), ws.Cells(rowchange, 67)) = ...

Easier to write this though

ws.Cells(rowchange, 8).Resize(1, 60) = ...
user_4788840
Related