simultaneous work on two workbook

Question

If I am working on two work books simultaneously for E.g. "1.xlsb" and "2. xlsb" I am running macro on "1.xlsb" and simultaneoulsy i work on "2.xlsb" in "1.xlsb" in macro there is a statement ThisWorkbook.ActiveSheet

Now the Problem is when i am running macro and go for another workbook in "2.xlsb" so in "1.xlsb" it can't Access ThisWorkBook.ActiveSheet so what can i do for this so i can work also on another workbook and macro is running in background in another workbook

Public Function GetBWTableInActiveSheet() As BoardwalkTable
    Dim ws As Worksheet
    Dim bwtResult As BoardwalkTable
    Set bwtResult = Nothing
    Set ws = ThisWorkbook.ActiveSheet

    For Each bwtResult In BoardwalkAPI.GetBoardwalkTables
        If StrComp(bwtResult.getrange.Parent.name, ws.name, vbTextCompare) = 0 Then
            Exit For
        End If
    Next bwtResult

    Set GetBWTableInActiveSheet = bwtResult
    Debug.Print bwtResult.getName
End Function

The easiest way is to open the workbooks in two different instances of Excel. But if you don't want to do that, you should change your code to avoid using things like `Select` and `Activate` (and `Selection` and `ActiveSheet` and `ActiveWorkbook`). — YowE3K, Oct 12 '17 at 06:22
Have a read of https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba — YowE3K, Oct 12 '17 at 06:29
Get a reference to the sheet when the macro starts, and use that reference in place of ActiveSheet. If you want more specific advice, post some actual code. — Tim Williams, Oct 12 '17 at 06:30
Include into the question at least the part of the code that is activating the worksheet, and the part that is calling the function. If we see those bits, we might be able to help suggest how to get rid of the `Activate` and `ActiveSheet` parts. (Considering that you are only using the **name** of the worksheet, and not any other aspect of the worksheet, it should be very easy to just pass that as a `String` parameter to the function.) — YowE3K, Oct 12 '17 at 08:08

score 2 · Accepted Answer · answered Oct 12 '17 at 06:22

2

You can use Workbooks("Name of your workbook").Sheets("Name of your sheet")

answered Oct 12 '17 at 06:22

Teasel

1,330
4
18
25

but everytime there is a different sheet – Jaydeep Bobade Oct 12 '17 at 06:24
i am working on excel workbook that is a project for client sometimes may be workbook name is also different – Jaydeep Bobade Oct 12 '17 at 06:26

simultaneous work on two workbook

1 Answers1