Attempting to change info in one workbook from a completely different workbook produces a Run-time error ‘9’: Subscript out of range.

[Computerman]

I have one workbook that contains one sheet that is a pivot table of a sharepoint page. The workbook keeps track of sites that are being worked every day, and the technicians that are doing the work. It mainly keeps track of sites that have not started yet, Scheduled. Sites that have a tech working, In Progress. Sites that are finished, Complete. And sites that are not able to be completed, Reschedule. I have a second workbook that I use to keep track of the deliverables that the techs are required to send in during the course of the work. What I want to do is use the status field in the ‘sharepoint’ workbook to control certain actions in the ‘deliverables’ workbook.
The code I have created is:
Private Sub cmdRefresh_Click()
Dim x As Integer
Dim y As Integer
Dim lastrow As Long
Dim Status As String

'Refesh the table the data is pulled from:
ThisWorkbook.RefreshAll

'determine the last row of the pivot table
lastrow = Sheet1.Range("A65536").End(xlUp).Row

For x = 9 To lastrow
y = x - 6
Status = Sheet1.Range("C" & x).Value

Select Case Status

Case "Scheduled"
Workbooks.Open Filename:="Darden Picture Status.xlsm"
Workbooks("Darden Picture Status.xlsm").Sheets("Sheet1").Range("K" & y).Interior.ColorIndex = 33
Workbooks("Darden Picture Status.xlsm").Save

Case "Complete"
'Workbooks.Open Filename:="C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm"
Workbooks("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm").Activate
Workbooks("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm").Sheets("Sheet1").Range("J" & y).Value = "X"
Workbooks("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm").Sheets("Sheet1").Range("K" & y).Interior.ColorIndex = 14
Workbooks("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm").Save

Case "Reschedule"
Workbooks.Open Filename:="Darden Picture Status.xlsm"
Workbooks("Darden Picture Status.xlsm").Sheets("Sheet1").Range("J" & y).Value = "R"
Workbooks("Darden Picture Status.xlsm").Save

Case "In Progress"
Workbooks.Open Filename:="Darden Picture Status.xlsm"
Workbooks("Darden Picture Status.xlsm").Sheets("Sheet1").Range("K" & y).Interior.ColorIndex = 48
Workbooks("Darden Picture Status.xlsm").Save

End Select

Next x

End Sub

In testing this code I get the error “Run-time error ‘9’: Subscript out of range.” When the code gets to the line: Workbooks("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm").Activate in the Complete case above.
I have placed a stop on the Select Case statement and used F8 to step through the code. It fails at the first instance of accessing the Darden Picture Status.xlsm line.
As a side note I tested this code on a pair of workbooks and know that it does work, the only difference is that the test workbooks have one word names with no spaces, where my working workbooks have multiple word names with spaces. I have carefully gone over the workbook names to be sure that I am not missing a space or any other misspelling. I have been going over the code and googling the error for the past week with no luck. The code is initiated by a command button on the 'sharepoint' workbook.
Has anyone seen this type of error in this scenario?

Thank you,
Computerman

 

[Kenneth Hobson]

Please paste code between code tags. Click # on toolbar to insert the tags.

Here is one Case. Note use of the workbook object.

Private Sub cmdRefresh_Click()
  Dim x&, y&, lastrow&, Status$, wb As Workbook
  
  'Refesh the table the data is pulled from:
  ThisWorkbook.RefreshAll
  
  'determine the last row of the pivot table
  lastrow = Sheet1.Range("A65536").End(xlUp).Row
  
  For x = 9 To lastrow
    y = x - 6
    Status = Sheet1.Range("C" & x).Value
    
    Select Case Status
      Case "Scheduled"
        Set wb = Workbooks.Open("C:\Users\webbmart\Documents\VBA\Darden Picture Status.xlsm")
        wb.Sheets("Sheet1").Range("K" & y).Interior.ColorIndex = 33
        wb.Save
      'Other cases
      Case Else
    End Select
  Next x
End Sub

 

[Computerman]

Mr. Hobson,
Thank you that does work. However, I forgot to mention that the Darden Picture Status workbook is already open on my desktop as I use it constantly throughout the day. Is there a way to modify the code so it doesn't force a close and reopen of the workbook?

 

[Kenneth Hobson]

You can use this to reference the workbook too:

Workbooks("Darden Picture Status.xlsm")

The xlsm part may not be needed for some. I think it will depend on Explorer file extensions being shown.

Use this to see if the workbook is open first:

Sub Test_IsWorkbookOPen()
  MsgBox IsWorkbookOpen("Personal.xls"), , "Personal.xls Open?"
  MsgBox IsWorkbookOpen("Personal.xlsb"), , "Personal.xlsb Open?"
  MsgBox IsWorkbookOpen("Personalx.xlsb"), , "Personalx.xlsb Open?"
End Sub

Function IsWorkbookOpen(stName As String) As Boolean
    Dim Wkb As Workbook
    On Error Resume Next ' In Case it isn't Open
    Set Wkb = Workbooks(stName)
    If Not Wkb Is Nothing Then IsWorkbookOpen = True
    'Boolean Function assumed To be False unless Set To True
End Function

 

[Computerman]

Re: Attempting to change info in one workbook from a completely different workbook produces a Run-time error ‘9’: Subscript out of range. (SOLVED)

Mr Hobson,
Thank you kindly for getting back to me. The xlsm extension is needed because both workbooks have VBA code in them. I actually found a way that keeps my workbook open while performing the modifications. Here is what my code looks like now (Just the Complete sections):

Case "Complete"
    For Each wb In Application.Workbooks
        If wb.Name = "Darden Picture Status.xlsm" Then
            wb.Sheets("Sheet1").Range("J" & y).Value = "X"
            wb.Sheets("Sheet1").Range("K" & y).Interior.Color = RGB(0, 176, 80)
        End If
    Next wb

this way my workbook stays open where I want it, on my desktop real estate, and modifies it per what the 'sharepoint' workbook is doing.

Again, thank you very much for your help. It pointed me in the right direction.

Computerman