Transfer data from one range to another using array

trux101

New Member
Joined
Feb 10, 2016
Messages
19
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
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.

Code:
[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

Code:
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):
Code:
[COLOR=#333333]Worksheets("Test").Range("A2:N" & LastLine).value = [/COLOR][COLOR=#333333]Worksheets("Raw Data").Range("A2:N" & LastLine).value[/COLOR]
note the .value
 
Last edited:

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,768
Office Version
365
Platform
Windows
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:

Code:
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

New Member
Joined
Feb 10, 2016
Messages
19
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,794
Messages
5,470,811
Members
406,726
Latest member
silverar

This Week's Hot Topics

Top