Subscript out of range?

[yokohama]

Hi guys!

I need some help.

Here is what I'm trying to do, I open 2 spreadsheet, spreadsheet1 and spreadsheet2

I put in my macro into one of it, say, spreadsheet1.

Then I run the macro

Sub Macro1()


    Application.ScreenUpdating = False


    Dim sp1 As String, sp2 As String


    sp1 = InputBox("Input first spreadsheet")
    sp2 = InputBox("Input second spreadsheet")
    
    Windows(sp2).Activate

I will key spreadsheet1 when the sp1 input box come out and spreadsheet2 when the sp2 input box come out. But after that it give me subscript out of range error at

Windows(sp2).Activate

Yet, sometimes it work. Even if I use

Workbooks(sp2).Activate

, it only sometimes will work.

How do I make it 100% working???

 

[JoeMo]

What exactly is sp2 when it doesn't work? Is it possible that workbook 2 is not open when it doesn't work?

 

[yokohama]

sp2 is a string variable.

I have opened both workbook, spreadsheet1 and spreadsheet2. so I key in spreadsheet2 when the sp2 input box appear.

even if I just use windows("spreadsheet2.xls").activate or workbooks("spreadsheet2.xls").activate, it still give me "subscript out of range" error though it is open

I'm not too sure if there is something wrong with my Excel

 

[Trebor76]

I'm not too sure if there is something wrong with my Excel

I would doubt that.

Two things to check:

• Make sure of the exact name of the workbook you're trying to reference through explorer i.e. press F2 on the file name within explorer and make sure that's what is being entered (there maybe a space for example),
• The workbooks have to be in the same session of excel for the code to work. See if you can see the other workbook when you click Switch Windows from the Windows tab from the View ribbon

HTH

Robert

 

[yokohama]

Oh! It's not in the switch windows! HOW?

 

[yokohama]

Oh. I realised how to do it and I solved the problem. Thanks!

But how do I make it such that when I open a new spreadsheet, it will always be under the same session??

 

[Trebor76]

Oh! It's not in the switch windows! HOW?

Because Excel (the program) has been opened twice with each one containing one of the files.

But how do I make it such that when I open a new spreadsheet, it will always be under the same session??

Ensure you open each file from the office button (top left-hand corner of any workbook) while in Excel instead of opening Excel again from the desktop and then opening the file.

Robert