VBA to duplicate large dataset using array-CodePudding

I have data on sheet A and want to duplicate it on sheet B. Because it is a lot of data, I do not want to use copy-paste. If I really simplify it, this is my code. My ranges change although I made it sort of fixed in this simplified code. I do not want to use something like range("A1:BBB100000") since my range will change. I get 1004 error "Application-defined or object-defined error". What am I doing wrong?

Dim origin(1 to 100000, 1 to 100000) as Variant
Dim dest(1 to 100000, 1 to 100000) as Variant

Set A=Worksheets("A")
Set B=Worksheets("B")
Vrow=100000

set origin=A.range(cells(1,1),cells(Vrow, Vrow))
set dest=B.range(cells(1,1),cells(Vrow, Vrow))
dest=origin

CodePudding user response：

You don't need the array. Only generate an array if your actually going to do any calculations on it. If you just want to do value -> value then that's what you do (as shown below).

Remember to always declare all your variables as well.

Dim A As Worksheet, B As Worksheet, Vrow As Long
Set A = Worksheets("A")
Set B = Worksheets("B")
Vrow = 100000

B.Range(B.Cells(1, 1), B.Cells(Vrow, Vrow)).Value = A.Range(A.Cells(1, 1), A.Cells(Vrow, Vrow)).Value

CodePudding user response：

Copy Range Values to Another Worksheet

Sub CopyValues()

    Const sName As String = "A"
    Const sFirstCellAddress As String = "A1"
    Const dName As String = "B"
    Const dFirstCellAddress As String = "A1"

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets(sName)
    Dim sfCell As Range: Set sfCell = sws.Range(sFirstCellAddress)
    Dim srg As Range: Set srg = sfCell.CurrentRegion
    
    Dim dws As Worksheet: Set dws = wb.Worksheets(dName)
    Dim dfCell As Range: Set dfCell = dws.Range(dFirstCellAddress)
    Dim drg As Range: Set drg = dfCell.Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value
    
End Sub


Sub CopyValuesShorter()

    Dim srg As Range
    Set srg = ThisWorkbook.Worksheets("A").Range("A1").CurrentRegion
    
    Dim drg As Range
    Set drg = ThisWorkbook.Worksheets("B").Range("A1") _
        .Resize(srg.Rows.Count, srg.Columns.Count)
    
    drg.Value = srg.Value
    
End Sub


Sub CopyValuesShortest()

    With ThisWorkbook.Worksheets("A").Range("A1").CurrentRegion
        ThisWorkbook.Worksheets("B").Range("A1") _
            .Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
    
End Sub