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
 

=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,554
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
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top