Switch to other workbook

Question

I am trying to build a code which would switch to the immediate other open workbook and copy data from there.

I can use workbook(1) and workbook(2) ,but problem is this index changes by sequence of opening workbooks.

So I want to put if function in it ,but doesnt work. Below is the code.

If ActiveWorkbook = Workbooks(1) Then

Workbooks(2).Activate

Else

Workbooks(1).Activate

End If

but it gives error 438 ,object doesn't support property or method.

Can you help me debug this?

(a) This is not a good way to do this. (b) You can resolve your immediate problem by using `If ActiveWorkbook.Name = Workbooks(1).Name Then` (c) Don't do it this way. (d) See http://stackoverflow.com/q/10714251/6535336 — YowE3K, Apr 30 '17 at 05:27
@ShaiRado - A simple bug to fix, but I see a lot more questions coming because of it. Sigh. — YowE3K, Apr 30 '17 at 05:53
I am going to try and explain the situation. I do not want to call workbook by names as there is one workbook where all the data from other workbooks are to be copied (This workbook has the macro buttons to run the macro) and there is the other workbook which is not a fixed single workbook and because we will fetch data from many workbooks and we cant keep naming them.So I want the user to just open two workbooks, one the main workbook with the macro where the data will be fetched. another the 2nd book from where the data will be fetched. Is there anyway I can do that? — Soura Chatterjee, Apr 30 '17 at 05:58
https://www.google.com.au/search?q=difference+between+active+workbook+and+thisworkbook&rlz=1C1CHFX_enAU686AU686&oq=difference+between+ActiveWorkbook+and+This&aqs=chrome.1.69i57j0.6864j0j7&sourceid=chrome&ie=UTF-8 — Robin Mackenzie, Apr 30 '17 at 06:00
http://stackoverflow.com/questions/35426907/difference-between-thisworkbook-name-and-activeworkbook-name-in-vba/35426954 — Robin Mackenzie, Apr 30 '17 at 06:01
Thanks that clears up one thing ,but the same error comes when I use If thisworkbook = workbook (1) Cant this be done? — Soura Chatterjee, Apr 30 '17 at 06:13
@SouraChatterjee see my answer below, let me know if it works as you intended. — Shai Rado, Apr 30 '17 at 08:31

score 2 · Answer 1 · answered Apr 30 '17 at 07:12

Try the code below, the last section of the Copy >> Paste is just an example how you copy Range("A1:E10") from "Sheet1" in DestWB to ThisWB "Sheet1" (without using Activate or Select) - you should be able to modify it quite easily.

Code

Option Explicit

Sub CopyThisWorkBOok()

Dim ThisWB      As Workbook
Dim DestWB      As Workbook
Dim wb          As Workbook
Dim i           As Long

i = Application.Workbooks.Count
If i <> 2 Then ' check if number of open workbooks is 2
    MsgBox "You need to have 2 open workbooks, currently there are " & i & " open workbooks", vbCritical
    Exit Sub
Else
    For Each wb In Application.Workbooks ' loop through all open workbooks
        If wb.Name <> ThisWorkbook.Name Then
            Set DestWB = wb
        Else
            Set ThisWB = ThisWorkbook
        End If
    Next wb
End If

' from here you start the part where you copy >> paste, there is no need to `Activate` or `Select` anything
DestWB.Worksheets("Sheet1").Range("A1:E10").Copy Destination:=ThisWB.Worksheets("Sheet1").Range("A2")

End Sub

@Soura Chatterjee have you read my answer above ? any feedback ? I saw that you never marked any of the previous answers you got here as "ANSWER", do you know how to mark as "ANSWER" ? — Shai Rado, Apr 30 '17 at 13:28

score 0 · Answer 2 · answered Apr 30 '17 at 05:30

0

You can use names of target objects, for example:

Workbooks("MyBook.xls").Worksheets("Sheet1").Activate

answered Apr 30 '17 at 05:30

Alexander

4,420
7
27
42

score 0 · Answer 3 · answered Apr 30 '17 at 07:15

Depending on what you are trying to do, the description is not very clear, you can use a variation of this code. You can define the workbook that contains the VBA as "ThisWorkBook" and go from there.

    Dim source_worksheet As Worksheet
    Set source_worksheet = ThisWorkbook.Worksheets("Sheet2")

    Dim target_worksheet As Worksheet
    Set target_worksheet = ActiveWorkbook.Worksheets("Sheet1")

    'Defines what sheet you are copying
    source_worksheet.Copy After:=target_worksheet

Switch to other workbook

3 Answers3