Vba Worksheet Switching

Question

I have two workbooks. I need to apply an if else condition on the 2nd workbook and the output of if else should be saved in the cell of 1st workbook using VBA. my code looks like :

Sub plan()
    Dim i As Integer, wb As Workbook

    For i = 4 To 100
        ActivateWB ("Bhandup Plan 11.xls")

        If Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 11).Value > 0 _
        Or Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 12).Value > 0 Then

            Workbooks("premium solver.xls").Sheets("AHMD").Cells(i, 1).Value = _
            Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 2).Value

        End If
    Next i
End Sub

So don't [Activate](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) it :) Work with the objects — Siddharth Rout, May 13 '15 at 09:18
You are probably referencing something that does not exist. What is `ActivateWB`? A sub you have written? — Olle Sjögren, May 13 '15 at 09:22
`tried using objects too but with no success` Can I see how did you do that? — Siddharth Rout, May 13 '15 at 09:23
I believe you need to open it first before you can use `.Activate`. Correct syntax is `Workbooks("Bhandup Plan 11.xls").Activate`. What does ActivateWB do? — Moritz Schmitz v. Hülst, May 13 '15 at 09:23
No, that was a referencing syntax i got while searching for answers... might be wrong ... can u please help me by editing the code ... my workbooks are "Bhandup Plan 11" and "premium solver" .... i need it desperately — fguy, May 13 '15 at 09:25
Just open both Workbooks and delete the `ActivateWB` line. Run macro. — Moritz Schmitz v. Hülst, May 13 '15 at 09:28
In that case do what @MoritzSchmitzv.Hülst mentioned in the last comment... — Siddharth Rout, May 13 '15 at 09:31
If you want to learn Excel VBA you are going to have to rewrite the code several times to improve it... :) — Olle Sjögren, May 13 '15 at 09:33
@OlleSjögren: i know dude but time is what i dont have ... :( — fguy, May 13 '15 at 09:34
`ActivateWB ("Bhandup Plan 11.xls")` does not belong in the code, the rest of the code appears to look okay, do your spellings for your workbooks and worksheets exactly match what is in your code and are both workbooks open? — Davesexcel, May 13 '15 at 09:36

score 1 · Answer 1 · answered May 13 '15 at 09:40

Open both Workbooks. Edit sub code:

Sub plan()
    Dim i As Integer

    For i = 4 To 100

        If Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 11).Value > 0 _
        Or Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 12).Value > 0 Then

            Workbooks("premium solver.xls").Sheets("AHMD").Cells(i, 1).Value = _
            Workbooks("Bhandup Plan 11.xls").Sheets("Sheet1").Cells(i, 2).Value

        End If
    Next i
End Sub

If you receive this error: Subscript out of range (Error 9), it means that either one of the names you provided is not right or that one of the workbooks isn't opened. So check for spelling, spaces etc. and make sure both workbooks are opened.

Glad I could help, you may now choose my answer as accepted answer. — Moritz Schmitz v. Hülst, May 13 '15 at 11:18

Vba Worksheet Switching

1 Answers1