How to paste array into a range [VBA] - Error 1004

Question

I've been trying to copy a simple table from 1 sheet into the last rows of a second sheet. Originally I tried with arrays, because both sheets have a different structure (columns are not in the same order, so I couldn't just copy & paste), but I always get error 1004. Now I gave up with that and changed the tables so both of them have the same structure and now I can just simply copy & paste, but I'm still getting the same error. This is what I have so far. I know it's a very simple thing but I don't know where I've got it wrong.

Sub testy()
Dim rowsIn, rowsOut As Long

With Worksheets("Sheet1")
    rowsIn = .Cells.SpecialCells(xlLastCell).Row
    .Range(.Cells(4, 1), .Cells(rowsIn, 3)).Copy
End With
With Worksheets("Sheet2")
    rowsOut = .Cells.SpecialCells(xlLastCell).Row
    .Range(.Cells(rowsOut + 1, 3)).PasteSpecial xlPasteValues
End With
End Sub

EDIT: Solved as per Tim Williams' suggestion. However, I'm still curious as to how this could be done with an array, as I originally intended. Assuming data in Sheet1 has columns in different order than in Sheet2, I tried using a temporary array to order the columns so I can just paste it. I managed to populate the array just fine, but can't figure out how to get the contents of the array into Sheet2. Added the code I used to populate (in a terribly inneficient manner) the array.

Sub testy2ElectricBoogaloo()
 Dim arr() As Variant
 Dim rowsIn, rowsOut, i As Long
    With Worksheets("Sheet1")
        rowsIn = .Cells.SpecialCells(xlLastCell).Row
        ReDim arr(1 To rowsIn - 3, 1 To 5)
'Array populated with a loop because columns are not in the same order, don't know if this is the most efficient method
        For i = 1 To UBound(arr)
            arr(i, 1) = "Constant1" 'data collected from other source
            arr(i, 2) = "Constant2" 'data collected from other source
            arr(i, 3) = .Cells(i + 3, 2).Value
            arr(i, 4) = .Cells(i + 3, 1).Value
            arr(i, 5) = .Cells(i + 3, 3).Value
        Next i
    End With

End Sub

Tim Williams · Accepted Answer · 2017-10-12 17:02:42Z

3

This is not valid:

.Range(.Cells(rowsOut + 1, 3)).PasteSpecial xlPasteValues

You could use:

.Cells(rowsOut + 1, 3).PasteSpecial xlPasteValues

You can do this without using copy/paste though:

Sub testy()

    Dim rowsIn, rowsOut As Long, rng As Range

    With Worksheets("Sheet1")
        rowsIn = .Cells.SpecialCells(xlLastCell).Row
        Set rng = .Range(.Cells(4, 1), .Cells(rowsIn, 3))
    End With

    With Worksheets("Sheet2")
        rowsOut = .Cells.SpecialCells(xlLastCell).Row
        .Cells(rowsOut + 1, 3)).Resize(rng.Rows.Count, _
                                       rng.Columns.Count).Value = rng.Value
    End With

End Sub

EDIT: using your arr example instead is quite similar:

    With Worksheets("Sheet2")
        rowsOut = .Cells.SpecialCells(xlLastCell).Row
        .Cells(rowsOut + 1, 3)).Resize(UBound(arr, 1), _
                                       UBound(arr, 2)).Value = arr
    End With

edited Oct 12, 2017 at 17:02

answered Oct 11, 2017 at 22:03

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Fernando J. Rivera Over a year ago

Thanks, that was definitely what I needed. Thanks for the quick reply - I'll now proceed to uninstall Office for not knowing something so basic.

Tim Williams Over a year ago

Not really that basic ;-)

Fernando J. Rivera Over a year ago

Sorry to keep asking in a closed Q, but how would I do it if I wanted to retrieve the data from an array instead of a range (as I originally wanted to do but gave up)?

Tim Williams Over a year ago

Your question is a bit vague - an array from where, constructed how?

Fernando J. Rivera Over a year ago

Sorry, I added a clarification to the original question, thanks for the help!

|

Collectives™ on Stack Overflow

How to paste array into a range [VBA] - Error 1004

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related