Array referencing to another worksheet

[Inactiver183192]

Hi I'm just trying to copy an array from worksheet inputs that so many rows down and then copy it to the worksheet "data" however my second line doesn't work. What would be wrong with this?

Also i then want to copy the array into Cell (9,4) or D9 but transpose my vArray. is there a quick way or should I just go the pastespecial transpose way?

Thanks all!


[CODE
Dim Last As Long
Dim vArray()

LastRow = Worksheets("Inputs").Range("C2").End(xlDown).Row
vArray = Worksheets("Inputs").Range(Cells(2, 3), Cells(LastRow, 3)).Value
Worksheets("Data").Range(Cells(9, 4), Cells(9, LastRow)).Value = vArray

[/CODE]

 

[Roli001]

Try this:

Sub Macro2()

    With Sheets("Inputs")
        LastRow = .[c65000].End(xlUp).Row
        .Range(.Cells(2, 3), .Cells(LastRow, 3)).Copy
    End With
    Sheets("Data").Range("G9").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Application.CutCopyMode = False
    
End Sub

 

[Inactiver183192]

Hi Roli! Thanks for the help I'll try that out. Just another question why is it that this works

Worksheets("Spot").Range("A2:P10").Value = Worksheets("Data").Range(Cells(9, 2), Cells(LastRow, LastCol)).Value

BUT not this

Worksheets("Spot").Range(Cells(9, 2), Cells(LastRow, LastCol)).Value = Worksheets("Data").Range(Cells(9, 2), Cells(LastRow, LastCol)).Value

If LastRow = 19 and last col =9
They should be referring to the same area? Thanks!

 

[Roli001]

The formula

Worksheets("Spot").Range(Cells(9, 2), Cells(LastRow, LastCol)).Value = Worksheets("Data").Range(Cells(9, 2), Cells(LastRow, LastCol)).Value

will not work as the above VBA code does not precicely indicate where the cells are.

To make the above formula work use the following:

Worksheets("Spot").Range(Worksheets("Spot").Cells(9, 2), Worksheets("Spot").Cells(LastRow, LastCol)).Value = Worksheets("Data").Range(Worksheets("Data").Cells(9, 2), Worksheets("Data").Cells(LastRow, LastCol)).Value

or

Set wsSp = worksheets("Spot")
Set wsDa = worksheets("Data")

     wsSp.Range(wsSp.Cells(9, 2), wsSp.Cells(LastRow, LastCol)).Value = wsDa.Range(wsDa.Cells(9, 2), wsDa.Cells(LastRow, LastCol)).Value

 

[Inactiver183192]

Ah perfect thanks that makes sense I just need to be very particular about the referencing. cheers!

 

[Roli001]

Glad to here I could help. Thanks for the feedback.