Error 1004 when copying and pasting from one worksheet to another in the same active workbook

Question

I'm an Excel VBA newbie trying to copy a range of cells from several worksheets and I want to paste this content on another worksheet in the same workbook by finding the last empty row and paste it there going one by one on each worksheet.

When I execute my code I get a 1004 error. I tried to test different sections of my code and I noticed that when I try to select a cell in a worksheet, I also get the same 1004 error but now says the following "Error in select method of Range class".

I tried using different methods to select this cell in my worksheet like "ThisWorkbook" "ActiveWorkbook" with "Sheets" and "Worksheets" and for whatever reason, I get the same error.

The code is in a module and I call the execution of this code using a button.

The interesting thing is that when I select the worksheet first and then select the range, I don't get this problem.

Like this:

ThisWorkbook.Sheets(3).Select
ThisWorkbook.Sheets(3).Range("A6").Select

I'd like to understand why this is the case so I can avoid using select in my code.

By the way, English is not my native language in case I didn't translate the errors that Visual Basic displays correctly.

Here is my code:

Sub ConsolidacionReportes()

Dim Fila As Long   ' to count each row in a for loop
Dim UltimaFila As Integer  ' Used to find the last row of a sheet
Dim Hojas As Integer ' used in a for loop to go through each sheet in the workbook
Dim RangoCeldas As String ' Range of cells
Dim BorrarTotales As Long ' another variable to count rows

For Hojas = 2 To 6

    UltimaFila = Sheets(Hojas).Cells(Sheets(Hojas).Cells.Rows.Count, "C").End(xlUp).Row - 1

    For Fila = UltimaFila To 5 Step -1
        RangoCeldas = "C"
        If Sheets(Hojas).Cells(Fila, "C") = "" Then
            Sheets(Hojas).Rows(Fila).Delete
        End If
    Next

Next

For Hojas = 2 To 6

BorrarTotales = Sheets(Hojas).Cells(5, 3).End(xlDown).Row + 3

     For Fila = BorrarTotales To 5 Step -1
        If Sheets(Hojas).Cells(Fila, "C") = "" Then
            Sheets(Hojas).Rows(Fila).Delete
        End If
    Next

Next

Reporte1.Range("A1:I5").Copy Calculos.Range("A1")

' When trying to select this range I get the error 1004: Error in select method of Range class

ThisWorkbook.Sheets(3).Range("A6").Select

For Hojas = 2 To 6

    UltimaFila = Sheets(Hojas).Cells(5, 1).End(xlDown).Row

    Let RangoCeldas = "A6:" & "B" & UltimaFila

' When executing the code I get another error 1004, this time the description is: Error defined by the application or the object

    Sheets(Hojas).Range(RangoCeldas).Copy Calculos.Cells(Calculos.Cells(5, 3).End(xlDown), 1)

Next

End Sub

You're getting the error as the third sheet in your Thisworkbook isn't active when you try and select a cell in it. — Darren Bartrup-Cook
Hi @Peh, thanks for your comment, I really can't get my head around this, in the first loop everything worked well without activating any worksheet but after that, the worksheets deactivate, would you be so kind to tell me why? or point me to a resource to learn about this? I've heard that using select and activate should be avoided when possible. Thanks again for your help. — César Reyes
Sorry Darren, I didn't write your name in my comment, I appreciate your help, I undestand I need to activate the sheet but I don't know why this also happens when I copy and paste content from one sheet to another... I want my for loop to go to all sheets, copy the content and paste it to another sheet in the same workbook. Any suggestions on how to efficiently do this? Thanks in advance for your help. — César Reyes

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2019-07-15T07:01:43

Sorry, a bit short on time so haven't been able to check completely. This code should get you closer to solving your problem.

Sub Test()

    Dim wrkSht As Worksheet 'Will hold a reference to the worksheet being copied from.
    Dim Fila As Long
    Dim UltimaFila As Long

    'Cycle through each worksheet, storing a reference in wrkSht.
    For Each wrkSht In ThisWorkbook.Worksheets
        With wrkSht
            'Check the codename of the sheet.
            'If it's the sheet being copied to ignore it, otherwise process it.
            'Not sure what Reporte1 is, but also ignoring it.
            Select Case .CodeName
                Case "Calculos", "Reporte1"
                    'Do nothing.
                Case Else
                    'Delete rows that have a blank column C.
                    'Could use a filter to speed this step up.
                    If UltimaFila >= 5 Then
                        For Fila = UltimaFila To 5 Step -1
                            If .Cells(UltimaFila, 3) = "" Then
                                .Rows(Fila).Delete
                            End If
                        Next Fila
                    End If

                    'Copy the range from wrksht to Calculos
                    .Range(.Cells(1, 1), .Cells(.Rows.Count, 2).End(xlUp)).Copy _
                        Destination:=Calculos.Cells(.Rows.Count, 1).End(xlUp)

            End Select
        End With
    Next wrkSht

End Sub

Error 1004 when copying and pasting from one worksheet to another in the same active workbook

1 Answers