Simple Activate workbooks code not working

[Mr2017]

Hi

I have some basic code which should activate two different open workbooks.

But I get a run time error ("Subscript out of range") when I execute the code.

Any ideas why? The code is below and I copied and pasted the file names into the code.

Thanks in advance.


Sub ReferringToWorkbooks()


Workbooks("Book2.xlsx").Activate
Workbooks("VBA Lesson 7 - (Wise Owl).xlsm").Activate


End Sub

 

[than_gold]

Hello Mr2017, Please check on the following points.
1] Name of the workbooks
2] Both file's should be open before you run this code
Regards,
Thangavel D

 

[admiral100]

Hi,

Change the name of the file from VBA Lesson 7 - (Wise Owl).xlsm to VBA Lesson 7 - (Wise Owl.xlsm or
VBA Lesson 7 - Wise Owl.xlsm

 

[Mr2017]

Hi admiral100

Thanks for the prompt response.

Although "Book2" was open, I hadn't saved the file.

So the code would work if I removed the file extension from the activate line, but it wouldn't work if I left the file extension in, as the file wasn't saved.

This is bizarre, as I was able to execute other things from a VBA video series I'm watching with a "Book2" file that was open but not saved.

I made a slight change to the second line, following your advice, and both lines are now working.

Thanks for your help!

Hi,

Change the name of the file from VBA Lesson 7 - (Wise Owl).xlsm to VBA Lesson 7 - (Wise Owl.xlsm or
VBA Lesson 7 - Wise Owl.xlsm

 

[Mr2017]

Switching between workbooks using the index number is still not working, however.

I have two open (and saved) workbooks open, but the code below does NOT switch between them.

Any ideas why? Thanks in advance.

Sub ReferringToWorkbooksByIndexNumber()


Workbooks(2).Activate


Workbooks(1).Activate


End Sub

 

[admiral100]

Can you add 2 line to your code and run

Count = Workbooks.CountMsgBox "Number of open Workbooks: " & Count

What is the number ?

By the way your code "switching" between the workbooks only once...
Its activate workbook 2 and then activate workbook 1....so you will always see the first workbook....

 

[Mr2017]

Hi

I added that code but it gave me a syntax error?

Can you add 2 line to your code and run

Count = Workbooks.CountMsgBox "Number of open Workbooks: " & Count

What is the number ?

By the way your code "switching" between the workbooks only once...
Its activate workbook 2 and then activate workbook 1....so you will always see the first workbook....