CODEWITHSUNDEEP
vbaexcel
skiaddict1
skiaddict1

Reputation: 525

VBA: How to reference cells in two workbooks at the same time

I'm writing a macro to move data from a report file into a master file. My initial version works, but it's slow and flashes a lot. This isn't surprising, since I'm using Workbook.Activate(). I did this deliberately, for initial simplicity and to aid in debugging.

However, now that it's working correctly, I'd like to stop using Workbook.Activate().

How can I refer to cells in two separate workbooks without actually going to each of them all the time? I don't mind a couple of flashes at the beginning, while doing the inits, but after that I'd like there to be no more.

I've tried this in a simple test sub:

Sub try()
    Dim c As Range
    Dim c1 As Range

    Workbooks(MASTER_FILE_NAME).Activate
    Set c = Cells(25, 1)
    Workbooks(REPORT_FILE_NAME).Activate
    Set c1 = Cells(10, 1)
    c.Value = c1.Value

    Set c = Cells(c.Row + 1, c.COLUMN)
    Set c1 = Cells(c1.Row + 1, c.COLUMN)
    c.Value = c1.Value
End Sub

but the third block simply sets both ranges to point to cells in the report file. Do I need to use named ranges? I'd really rather not. Is there perhaps another way?

Upvotes: 1

Views: 4036

Answers (3)

R3uK
R3uK

Reputation: 14537

There won't be any screen activity if you don't use .Activate or .Select, so no need to turn off the screen update for the moment (only if you have a lot of data to transfer)

You should simply create Workbook and Worksheet objects to let the code which one you are using :

Sub try()
    Dim wB1 As Workbook
    Dim wB2 As Workbook
    Dim wS1 As Worksheet
    Dim wS2 As Worksheet
    Dim c1 As Range
    Dim c2 As Range

    Dim MASTER_FILE_NAME As String
    MASTER_FILE_NAME = "Workbook_MASTER_Name.xlsm"
    Dim REPORT_FILE_NAME As String
    REPORT_FILE_NAME = "Workbook_REPORT_Name.xlsm"


    'Set wB1 = ThisWorkbook
    'Set wB1 = ActiveWorkbook
    Set wB1 = Workbooks(MASTER_FILE_NAME)
    Set wB2 = Workbooks(REPORT_FILE_NAME)

    Set wS1 = wB1.Sheets("Master_Sheet_Name")
    Set wS2 = wB2.Sheets("Report_Sheet_Name")


    Set c1 = wS1.Cells(25, 1)
    Set c2 = wS2.Cells(10, 1)

    'Put the data of the master in report
    c2.Value = c1.Value

    Set c1 = c1.Offset(1, 0)
    Set c2 = c2.Offset(1, 0)

    c2.Value = c1.Value
End Sub

Or you can do this with a Range of Cells directly (much more efficient and fast!) :

Sub try_ranges()
    Dim wB1 As Workbook
    Dim wB2 As Workbook
    Dim wS1 As Worksheet
    Dim wS2 As Worksheet
    Dim c1 As Range
    Dim c2 As Range
    Dim LastRow1 As Long


    Dim MASTER_FILE_NAME As String
    MASTER_FILE_NAME = "Workbook_MASTER_Name.xlsm"
    Dim REPORT_FILE_NAME As String
    REPORT_FILE_NAME = "Workbook_REPORT_Name.xlsm"


    'Set wB1 = ThisWorkbook
    'Set wB1 = ActiveWorkbook
    Set wB1 = Workbooks(MASTER_FILE_NAME)
    Set wB2 = Workbooks(REPORT_FILE_NAME)

    Set wS1 = wB1.Sheets("Master_Sheet_Name")
    Set wS2 = wB2.Sheets("Report_Sheet_Name")

    'Get the last used row in column A in your master sheet
    LastRow1 = wS1.Range("A" & wS1.Rows.Count).End(xlUp).Row

    'Take the whole column in master sheet
    Set c1 = wS1.Range(wS1.Cells(25, 1), wS1.Cells(LastRow1, 1))

    'Resize the range in report sheet to fit what you have taken in master sheet
    Set c2 = wS2.Cells(10, 1).Resize(c1.Rows.Count, 1)

    'Transfer the values with high efficiency! ;)
    c2.Value = c1.Value
End Sub

Upvotes: 1

Preston
Preston

Reputation: 8177

To stop the flashing you can use this (Although it will stop with a lack of workbook changing, it's a good habit to get into, and should improve performance):

With Application.Excel
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With

To answer your question about simultaneous referencing, no it's not possible exactly simultaneously, but Pierre is correct with:

Set c = Workbooks(MASTER_FILE_NAME).sheets("azzeetr").Cells(25, 1)
Set c1 = Workbooks(REPORT_FILE_NAME).sheets("dfdsfvsdfvs").Cells(10, 1)

using this you won't change between active sheets, just reference cells in a different book.

I'd use this:

With Application.Excel
    .ScreenUpdating = False
    .DisplayStatusBar = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
End With
Set c = Workbooks(MASTER_FILE_NAME).sheets("azzeetr").Cells(25, 1).offset(1,0)
Set c1 = Workbooks(REPORT_FILE_NAME).sheets("dfdsfvsdfvs").Cells(10, 1).offset(1,0)
c.Value = c1.Value
With Application.Excel
    .ScreenUpdating = True
    .DisplayStatusBar = True
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
End With

Upvotes: 1

Pierre
Pierre

Reputation: 1046

Sub try()
    Dim c As Range
    Dim c1 As Range

    Set c =         Workbooks(MASTER_FILE_NAME).sheets("azzeetr").Cells(25, 1)

    Set c1 = Workbooks(REPORT_FILE_NAME).sheets("dfdsfvsdfvs").Cells(10, 1)
    c.Value = c1.Value

    Set c = c.offset(1)
    Set c1 = c1.offset(1)

End Sub

remember "cells" assumes "activewrokbook.activesheet."

Upvotes: 2

Related Questions