CODEWITHSUNDEEP
vbaexcel
Pierre44
Pierre44

Reputation: 1741

Excel VBA: Run-Time error 1004 calling Workbooks

I have found different Run-Time Error 1004 questions, but none of them were totally appropriate.

I get an error 1004 "Application-defined or object defined error" when using workbooks (last Row).

Dim mainWB  As Workbook
Dim mainWS  As Worksheet
Set mainWB = ActiveWorkbook
Dim wb As Workbook
Dim WBname As String

For Each wb In Workbooks
            If Left(wb.Name, 4) = "D_SO" And Len(wb.Name) > 30 Then
                WBname = wb.Name
                Exit For
            End If
Next

mainWB.Sheets(1).Range(Cells(1, 1), Cells(1600, 300)).Value = Workbooks(WBname).Sheets(1).Range(Cells(1, 1), Cells(1600, 300)).Value

None of my Workbooks objects in the last row seem to be working, eventhough the names are right. Can you find my mistake?

Upvotes: 0

Views: 112

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19077

When you are using Range object reference of that type: Range(LeftTopCell, RightBottomCell) you have to care of what is in fact inside parenthesis.

In your situation you have always Activesheet cells which is not what you want to have. Therefore you have to have full reference to cells in this way:

mainWB.Sheets(1).Range(mainWB.Sheets(1).Cells(1, 1), _
                       mainWB.Sheets(1).Cells(1600, 300)).Value = 
        Workbooks(WBname).Sheets(1).Range(Workbooks(WBname).Sheets(1).Cells(1, 1), _ 
                                     Workbooks(WBname).Sheets(1).Cells(1600, 300)).Value

To make it shorter you could think of using Range.Copy method instead.

Upvotes: 2

Related Questions