Excel VBA Run Time Error: Application-defined or object-defined error (1004)

0
votes

I have a certain Loop in specific sub and module. I want to do the same action in a different sub and module with small change in the range where I paste the data, but it runs to me as an error. I tried to change the names of the variables for the range but it didn't work.

someone has ideas?

'part 3 - add new data as we did in the first sub
Dim Company As Range
Dim Companies As Range
Set Companies = ThisWorkbook.Worksheets("Data").Range("A2", Range("A2").End(xlDown))

For Each Company In Companies
    With ThisWorkbook.Worksheets(Company.Value2)
        .Activate
        .Range("H2").End(xlDown).Select
        .Range(Selection, Selection.End(xlToRight)).Copy
    End With
    With ThisWorkbook.Worksheets("Data")
        .Activate
        .Range("A2", Range("A2").End(xlDown)).Find(Company.Value2, , xlValues, xlWhole).Activate
    End With
    ActiveCell.Offset(0, 4).PasteSpecial (xlPasteValues)
Next Company

error in line:

Set Companies = ThisWorkbook.Worksheets("Data").Range("A2", Range("A2").End(xlDown))

1
vbaexcel
On which line are you getting the error ? – Imran Malek
Try explicitly declaring all ranges you use, e.g. change ThisWorkbook.Worksheets("ðúåðéí").Range("A2", Range("A2").End(xlDown)) to ThisWorkbook.Worksheets("ðúåðéí").Range(ThisWorkbook.Worksheets("ðúåðéí").Range("A2"), ThisWorkbook.Worksheets("ðúåðéí").Range("A2").End(xlDown)) – eirikdaude
@eirikdaude I didn't understood what you meant, can you write it down as an answer? – Rafael Osipov

1 Answers

0
votes

Try the code below:

With ThisWorkbook.Worksheets("Data")
    Set Companies = .Range("A2", .Range("A2").End(xlDown))
End With

Unless, you don't have a worksheet named "Data"