Make range of cells equal to another range of cells in different workbooks

Question

I would like to take a range of values from multiple workbooks and enter it into the next blank row of a master workbook. I’m trying to streamline this code as much as possible as there will be multiple workbooks that will be entered into its corresponding tab in the Masterwork book.

Example:

Column C (MasterWorkbook,Sheet 2) = Column A (Workbook 1, Sheet 1)

Questions:

Can you make a range of cells = to another range if the amount of rows will change daily?
Is there an easy way to reference workbooks and worksheets variables within the code? For example: wk1.sht1.Origin.Value = wk2.sht2.Destination.Value

The code:

Sub CopyData()


Dim wk1 As Workbook
Dim wk2 As Workbook
Dim sht1 As Worksheet
Dim sht2 As Worksheet
Dim Origin As Range
Dim Destination As Range

Set wk1 = Workbooks("Device")
Set wk2 = Workbooks("Pri Access Master)
Set sht1 = Worksheets(1)
Set sht2 = Worksheets("Device")

Set Origin = Range("B2").End(xlDown)
Set Destination = Range("D120000").End(xlUp).Offset(1, 0)

'(Isn't working)Copy data in column B and paste in next blank row of another    workbook in another column
wk2.sht2.Destination.Value = wk1.sht1.Origin.Value


End Sub

To set ranges equal to each other, they have to be the same size. — findwindow

Tim Williams Tim Williams · Accepted Answer · 2016-03-30T15:51:35

Your referencing of objects is incorrect - you dont need to include the full "path" once you have set the Range objects:

Sub CopyData()


    Dim wk1 As Workbook
    Dim wk2 As Workbook
    Dim sht1 As Worksheet
    Dim sht2 As Worksheet
    Dim Origin As Range
    Dim Destination As Range

    Set wk1 = Workbooks("Device")
    Set wk2 = Workbooks("Pri Access Master")
    Set sht1 = wk1.Worksheets(1)
    Set sht2 = wk2.Worksheets("Device")
    Set Destination = sht2.Range("D120000").End(xlUp).Offset(1, 0)

    'EDIT - dynamic copy range
    Set Origin = sht1.Range(sht1.Range("B2"), sht1.Cells(sht1.Rows.Count, "B").End(xlUp))



    Destination.Resize(Origin.Rows.Count, Origin.Columns.Count).Value = _
                                                           Origin.Value
End Sub

Make range of cells equal to another range of cells in different workbooks

1 Answers