Transfer data from one range to another using array

[trux101]

Hi, can anyone spot the error in my ways on this? I've been on it for hours!

Essentially looking to copy data from Raw Data to Table5 which is on a separate sheet - but without using copy and paste that is taking way too long for my 20,000 rows and 85 columns of data. Any help will be greatly appreciated!

Public Sub Copy_3()

Dim DestRng As Variant, Virt As Variant

With Worksheets("Raw Data")
Set LastData = .Cells(.Rows.Count, "A").End(xlUp)
LastLine = LastData.Row
End With

Virt = Worksheets("Raw Data").Range("A2:N" & LastLine)
DestRng = Worksheets("Test").Range("Table5")

DestRng = Virt
End Sub

 

[=ODIN=]

I think I understand what you are describing, although I've never attempted it with a table range. Your code here might not be working for a couple reasons.

[COLOR=#333333]Virt = Worksheets("Raw Data").Range("A2:N" & LastLine)[/COLOR]
[COLOR=#333333]DestRng = Worksheets("Test").Range("Table5")[/COLOR]

here, when making a vairable = to an object, in this case a range, you must use set

set [COLOR=#333333]Virt = Worksheets("Raw Data").Range("A2:N" & LastLine)[/COLOR]
[COLOR=#333333]set DestRng = Worksheets("Test").Range("Table5")[/COLOR]

For making one ranges valus = another's with without using copy/paste i've used this syntax (which assumes ranges are the exact same size -- so no idea how a table would work):

[COLOR=#333333]Worksheets("Test").Range("A2:N" & LastLine).value = [/COLOR][COLOR=#333333]Worksheets("Raw Data").Range("A2:N" & LastLine).value[/COLOR]

note the .value

 

[StephenCrump]

You'll need to make the destination range the same size as Virt to accommodate all the values. Here's one way you could do this:

With Worksheets("Raw Data")
    Virt = .Range("A2:N" & .Cells(.Rows.Count, "A").End(xlUp).Row).Value
End With

Worksheets("Test").Range("Table5").Resize(UBound(Virt), UBound(Virt, 2)).Value = Virt

 

[trux101]

Re: Transfer data from one range to another using array [SOLVED]

Thank you StephenCrump, ODIN! It's worked. For anyone ever looking:
Public Sub Copy()
Dim Virt As Variant

With Worksheets("Raw Data")
Set LastData = .Cells(.Rows.Count, "A").End(xlUp)
LastLine = LastData.Row
Virt = .Range("A2:N" & .Cells(.Rows.Count, "A").End(xlUp).Row).value
End With

Worksheets("Test").Range("Table5").Resize(UBound(Virt), UBound(Virt, 2)).value = Virt
End Sub