excel vba range copy from one sheet to another (error 1004)

Question

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 :

"Worksheet1" with the data I want to copy
"Feuil1" where I want to paste data with a specific layout

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*. — , Apr 07 '18 at 20:56
Suggest you read [Is the . in .Range necessary when defined by .Cells?](https://stackoverflow.com/questions/36368220/is-the-in-range-necessary-when-defined-by-cells) for a better understanding of defining a range using cells. — , Apr 07 '18 at 20:58
I didn't say that it would; I said that *b* was unnecessary. — , Apr 07 '18 at 21:43

score 1 · Answer 1 · answered Apr 07 '18 at 20:51

1

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

answered Apr 07 '18 at 20:51

QHarr

83,427
12
54
101

"i" was not defined but I still have the 1004 error – TheLegend27 Apr 07 '18 at 21:40
did you intend for the copy to rows to increase beyond the initial copied 3000? – QHarr Apr 07 '18 at 21:48
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 07 '18 at 22:23

score 0 · Answer 2 · answered Apr 07 '18 at 20:52

0

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

answered Apr 07 '18 at 20:52

Do you actually have a worksheet named *Worksheet1* or should that be `with Worksheets("Sheet1")` ? – Apr 07 '18 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 07 '18 at 21:19
after some tries, no issue with the sheet name – TheLegend27 Apr 07 '18 at 21:42

score 0 · Answer 3 · answered Apr 07 '18 at 22:43

0

Try this one:

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(Cells(a, 1), Cells(a + 2999, 1)).Copy Worksheets("Feuil1").Cells(2, b)

    a = a + 3000
    b = b + 1

Next i

answered Apr 07 '18 at 22:43

A.Chlechko

16
2

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 07 '18 at 22:54
If macro is triggered when different sheet is activated, you can add Worksheets(“Worksheet1”).Activate before the loop. – A.Chlechko Apr 08 '18 at 08:50

score 0 · Accepted Answer · answered Apr 07 '18 at 22:52

Option Explicit

Sub test()
  Dim a As Integer
  Dim b As Integer
  Dim i As Integer
  Dim val4 As Integer

  val4 = 4
  a = 2
  b = 1

  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...

score 0 · Answer 5 · answered Apr 08 '18 at 11:47

you may want to speed it up by pasting values only instead of ranges

Sub test()
    Const chunksLength = 3000 ' define here your "chunks" length

    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
        Next
    End With
End Sub

excel vba range copy from one sheet to another (error 1004)

5 Answers5