can't seem to activate an open workbook
 

I am flipping between 2 workbooks and getting an error when the vba code
reaches the line where it is supposed to pick the second workbook. Can
anyone tell me why it gives me this error? I've tried to
workbooks(filename).activate instead of windows(filename).activate and that
did not help much. Here is the error I get...

Run-time error '9':
Subscript out of range

....tells me subscript is out of range, as if I'm picking an element of an
array that is not part of the array. The code is...


Sub PPP_PORreport()

Dim stevefrieswb As String
Dim wb As Workbook
Dim PORPPPwb As String

Application.DisplayAlerts = False
Application.ScreenUpdating = True

For Each wb In Application.Workbooks
wb.Activate
If Left(wb.Name, 7) = "Monthly" Then
stevefrieswb = InputBox("What is the name of Steve Fries'
file/workbook?", "STEVE FRIES' WORKBOOK NAME", wb.Name)
If stevefrieswb = "" Then
MsgBox ("Please open Steve Fries' PPV Monthly Detail
workbook for your desired location & then re-run macro.")
Exit Sub
End If
End If
If Left(wb.Name, 7) = "POR PPP" Then
PORPPPwb = InputBox("What is the name of your POR PPP
file/workbook?", "POR PPP WORKBOOK NAME", wb.Name)
If PORPPPwb = "" Then
MsgBox ("Please open the POR PPP workbook for your desired
location & then re-run macro.")
Exit Sub
End If
End If
Next


Windows(PORPPPwb).Activate
Sheets("Future PPV").Select


Windows(stevefrieswb).Activate ' This is where the error occurs.
End Sub


Can someone tell me why this occurrs?
Thanks,
dantee.

can't seem to activate an open workbook
 

Maybe there isn't a window or workbook open with that name???

Are you positive that the user is typing the correct name?

If the user isn't including the extension (.xls???), then I'd append it to that
workbooks(stevefrieswb & ".xls").Activate

But I'd double check the user's typing to make sure that a workbook with that
name is open.



dantee wrote:

I am flipping between 2 workbooks and getting an error when the vba code
reaches the line where it is supposed to pick the second workbook. Can
anyone tell me why it gives me this error? I've tried to
workbooks(filename).activate instead of windows(filename).activate and that
did not help much. Here is the error I get...

Run-time error '9':
Subscript out of range

...tells me subscript is out of range, as if I'm picking an element of an
array that is not part of the array. The code is...

Sub PPP_PORreport()

Dim stevefrieswb As String
Dim wb As Workbook
Dim PORPPPwb As String

Application.DisplayAlerts = False
Application.ScreenUpdating = True

For Each wb In Application.Workbooks
wb.Activate
If Left(wb.Name, 7) = "Monthly" Then
stevefrieswb = InputBox("What is the name of Steve Fries'
file/workbook?", "STEVE FRIES' WORKBOOK NAME", wb.Name)
If stevefrieswb = "" Then
MsgBox ("Please open Steve Fries' PPV Monthly Detail
workbook for your desired location & then re-run macro.")
Exit Sub
End If
End If
If Left(wb.Name, 7) = "POR PPP" Then
PORPPPwb = InputBox("What is the name of your POR PPP
file/workbook?", "POR PPP WORKBOOK NAME", wb.Name)
If PORPPPwb = "" Then
MsgBox ("Please open the POR PPP workbook for your desired
location & then re-run macro.")
Exit Sub
End If
End If
Next


Windows(PORPPPwb).Activate
Sheets("Future PPV").Select


Windows(stevefrieswb).Activate ' This is where the error occurs.
End Sub

Can someone tell me why this occurrs?
Thanks,
dantee.

--

Dave Peterson