Array - cannot paste values into diffrend ws

[Captain_Jack_T]

Hi guys

I am using an array in order to copy the values of a some cells into another sheet. Does anyone know why it breaks? Thanks in advance!

Sub TrySomeArrays()

    Dim RollerCoaster() As Variant
    Dim Dimension1 As Long, Dimension2 As Long
    
    Sheet1.Activate
    
    Dimension1 = Range("A2", Range("B2").End(xlDown)).Cells.Count - 1
    Dimension2 = Range("A2", Range("A2").End(xlToRight)).Cells.Count - 1
    
    ReDim RollerCoaster(0 To Dimension1, 0 To Dimension2)
    
    For Dimension1 = LBound(RollerCoaster, 1) To UBound(RollerCoaster, 1)
        For Dimension2 = LBound(RollerCoaster, 2) To UBound(RollerCoaster, 2)
[COLOR=#daa520]            Sheet2.Range("A1").Select
            ActiveCell.Offset(Dimension1, Dimension2).Value = RollerCoaster(Dimension1, Dimension2).Value[/COLOR]
        Next Dimension2
    Next Dimension1
    
End Sub

 

[JackDanIce]

What is the error message that you get and on what line is it?

You cannot select a cell on a specified sheet, if that sheet isn't active, so consider changing:

Sheet2.Range("A1").Select

To

Sheet2.Select
Range("A1").Select

I think you need to remove .Value, e.g.

ActiveCell.Offset(Dimension1, Dimension2).Value = RollerCoaster(Dimension1, Dimension2)

vs

ActiveCell.Offset(Dimension1, Dimension2).Value = RollerCoaster(Dimension1, Dimension2).Value

Since .Value is not a property of an array object, .Value is a property of a range object

You shouldn't need to use .Activate or .Select to read or write cell values, and usually if you're printing the entire array out, there isn't a need to loop through it, try:

Sub TSA()
    
    Dim arr()   As Variant
    Dim LR      As Long
    Dim LC      As Long
    
    With Sheet1
        LR = .Cells(.Rows.count, 2).End(xlUp).row
        LC = .Cells(2, .Columns.count).End(xlToLeft).column
        arr = .Cells(2, 1).Resize(LR - 1, LC - 1).Value
    End With
    
    Sheet2.Cells(2, 1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
    
    Erase arr

End Sub