Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I know there are a lot of topics related to the the error 1004 (object or app error) when you try to copy paste range from one sheet to another, but I do not understand something (I am still a beginner with VBA).

I have a Excel file with 2 sheets :

To understand my problem, take a look at my code

    Dim a As Integer
    Dim b As Integer
    Dim val4 as integer
    val4 = 4 
    a = 2
    b = 1
    For i = 1 To val4
        Worksheets("Worksheet1").Range(Worksheets("Worksheet1").Cells(a, 1), Cells(a + 2999, 1)).Copy Destination:=Worksheets("Feuil1").Range(Worksheets("Feuil1").Cells(2, b), Cells(a + 2999, b))
        a = a + 3000
        b = b + 1
    Next i
Note : in the full code, val4 was already used before this part.
When I try to run it, I obtain error 1004 (object or app error).
From what I have read, I believe the problem comes from "Cells" property where the working sheet must be specified (unlike range) but I do not understand why my code failed for copying.
Could someone explain it to me?
                I don't know if this would make a difference in a real non-testing operation but you could get rid of b and substitute i.
– user4039065
                Apr 7, 2018 at 20:56
                Suggest you read Is the . in .Range necessary when defined by .Cells? for a better understanding of defining a range using cells.
– user4039065
                Apr 7, 2018 at 20:58
Can you swop as below and fully qualify sheet references as this was causing issue
Option Explicit
Sub test()
    Dim a As Long
    Dim b As Long
    Dim val4 As Long
    val4 = 4
    a = 2
    b = 1
    Dim i As Long
    For i = 1 To val4
        With Worksheets("Worksheet1")
            Worksheets("Feuil1").Range(Worksheets("Feuil1").Cells(2, b), Worksheets("Feuil1").Cells(a + 2999, b)) = .Range(.Cells(a, 1), .Cells(a + 2999, 1))
        End With
        a = a + 3000
        b = b + 1
    Next i
End Sub
                I made some scientific experiments and manage to combine all my results (txt files) in one excel file, but they are all following up in one column. Every 3000 rows correspond to an experiment, and I want to get one column per experiment (I have 100 000+ results!)
– TheLegend27
                Apr 7, 2018 at 22:23
  For i = 1 To val4
    WorkSheet1.Range(WorkSheet1.Cells(a, 1), WorkSheet1.Cells(a + 2999, 1)).Copy Feuil1.Range(Feuil1.Cells(2, b), Feuil1.Cells(a + 2999, b))
    a = a + 3000
    b = b + 1
  Next i
End Sub
You were on the right rack with what you said about the Cells bit messing it up.
The range(cells.....) must have the internal cells bit properly referenced. So each use of the word cells should be changed to worksheets(enter worksheet name in here).cells and then it will work.
The solution I've posted above is slightly neater I think due to it not using the tab labels that users can change. So in the VBA editor in the properties window you can give the sheet object a name.  I've called the first sheet worksheet1 and the second Feuil1 (Note ..this is not the tab name) now you can reference these objects directly in your code as I've shown above.
It should all work...  
You are only specifying one of the parent worksheet references to the Cells that define Range. I'd suggest a With .... End With block and you only require the top-left cell for the destination to a Copy.
with Worksheets("Worksheet1")
    For i = 1 To val4
        .Range(.Cells(a, 1), .Cells(a + 2999, 1)).Copy _
            Destination:=Worksheets("Feuil1").Cells(2, b)
        a = a + 3000
        b = b + 1
    Next i
end with
                Do you actually have a worksheet named Worksheet1 or should that be with Worksheets("Sheet1") ?
– user4039065
                Apr 7, 2018 at 21:15
                My sheet name is Worksheet1 I tried Sheet1 and I got an error 9 EDIT : in my language "Feuil1" means "Worksheet1" calling issue?
– TheLegend27
                Apr 7, 2018 at 21:19
For i = 1 To val4
    Worksheets("Worksheet1").Range(Cells(a, 1), Cells(a + 2999, 1)).Copy Worksheets("Feuil1").Cells(2, b)
    a = a + 3000
    b = b + 1
Next i
                This code will work if you have the Worksheet1 sheet selected before you run the code. If you select anyother sheets then the .range ( cells(a,1) .......) will complain as the internal cells call isn't referenced properly.
– perfo
                Apr 7, 2018 at 22:54
                If macro is triggered when different sheet is activated, you can add Worksheets(“Worksheet1”).Activate before the loop.
– A.Chlechko
                Apr 8, 2018 at 8:50
    Dim i As Long
    With Worksheets("Feuil1").UsedRange.Columns(1)
        For i = 1 To .Rows.Count \ chunksLength + IIf(.Rows.Count Mod chunksLength > 0, 1, 0)
            .Offset(, i).Resize(chunksLength).Value = .Resize(chunksLength).Offset((i - 1) * chunksLength).Value
    End With
End Sub
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.