Make changes to another workbook - Runtime error 9

[GrantAstley]

Hi
I am trying to use a button to check weather a workbook is open, if it is not open then open it and make changes to that work book in a specific sheet.

The button
- Saves the Quote now as an invoice as a PDF file
- Inserts the Quote No (Which is unique) into the
Workbook - Astleys Electrical, Sheet - Jobs, Cell - I2
it then uses the formula in - I3 formular =7+(MATCH(I2,B8:B1000,0))
to determine which row to add data to and to change the colour of certain cells in that row
Then the VBA program gets this number (Rowx) and uses it to make the changes.
- Makes changes to Astleys Electrical workbook and saves them
- Saves the current workbook

Then once changes are made if the workbook was not open then close it, if it was open then leave it open.

I am getting a runtime error 9 - Script out of range on line
MasterWb.JobsSh.Range("I2").Value = Wb.Sheets("Quote").Range("H4").Value 'Input Quote No so spreadsheet can calc which row to be on

Any help with this problem would be greatly appreciated.

Private Sub Quote_Accepted_PB_Click()

Sheets("Invoice").Visible = True


Dim WbOpen As Boolean
Dim MasterWb As Workbook
Dim JobsSh As Worksheet


Dim Wb As Workbook
Set Wb = ThisWorkbook


Dim Hyper_Name As String
Dim Hyper_Loc As String
Dim Save_Name As String


'Example Saved Name: I-50-2, 02-01-2013, Grant Astley
Save_Name_PDF = Wb.Sheets("Invoice").Range("G4").Value & ", " & Format(Now, "yyyy-mm-dd") & ", " & Wb.Sheets("Invoice").Range("C8").Value & ".pdf"

Hyper_Name = Wb.Sheets("Invoice").Range("G4").Value
Hyper_Loc = "C:\Users\Grant\Documents\Astleys Electrical\Invoices\" & Save_Name_PDF


'Save Invoice as PDF
Wb.Sheets("Invoice").Select
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="C:\Users\Grant\Documents\Astleys Electrical\Invoices\" & Save_Name_PDF, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False


' Check if Astleys Electrical workbook is open
On Error Resume Next
Set MasterWb = Workbooks("Astleys Electrical.xlsm")
On Error GoTo 0
If MasterWb Is Nothing Then
' If workbook was NOT open, we'll open it
' We'll use the parameter WbOpen to remember whether the workbook was open or not
Set MasterWb = Workbooks.Open("C:\Users\Grant\Documents\Astleys Electrical\Astleys Electrical.xlsm", ReadOnly:=False) 'Open Workbook
WbOpen = False
Else
WbOpen = True
End If


'Find which row to use
Dim Rowx As String
MasterWb.JobsSh.Range("I2").Value = Wb.Sheets("Quote").Range("H4").Value  'Input Quote No so spreadsheet can calc which row to be on
MasterWb.JobsSh.Calculate                                                 'Force Sheet to perform calculations
Rowx = MasterWb.JobsSh.Range("I3").Value                                  'Make Rowx = calculated row from worksheet


'Change color of Quote Status to green
MasterWb.JobSh.Range(Cells(Rowx, 2)).Interior.ColorIndex = 4

'Insert date quote was accepted and quote date expiry to jobs sheet
MasterWb.JobsSh.Range(Cells(Rowx, 9)).Value = Format(Date, "dd-mmm-yyyy")
MasterWb.JobsSh.Range(Cells(Rowx, 10)).Value = Format(Date + 31, "dd-mmm-yyyy")


'Add hyperlink to Invoice from Master Jobs list
MasterWb.JobsSh.Hyperlinks.Add Anchor:=MasterWb.JobsSh.Range(Cells(Rowx, 3)), _
                                       Address:=Hyper_Loc, _
                                       TextToDisplay:=Hyper_Name


' If Astleys Electrical - Master workbook was NOT open, we'll close it
If WbOpen = False Then MasterWb.Close SaveChanges:=True
'If it was open then we will just save it
if WbOpen = True Then MasterWb.Save


'Set visibility of sheets on current job wb
Wb.Sheets("Test Results").Visible = True
Wb.Sheets("Quote").visibility = False


'Save job workbook now with invoice visible
Wb.Save


End Sub

[p45cal]

I don't see anywhere that you've assigned a sheet to JobSh.
Other than that, subscript out of range error is an indication that something in parentheses is incorrect on that line: "Quote" is the likely candidate; is it the exact same name as on the sheet tab?,

[GrantAstley]

I dont know how I did not see that. Thankyou however now I get the runtime error on the line where I set JobSh

Set JobSh = Sheets("Jobs")

This would indicate that that the error occurs on with the Jobs sheet yes? I get the error even if I reference it like

Set JobSh = Sheet11

This sheet is in the Astleys Electrical workbook. Jobs is an exact match to what the sheet is called and I have referenced it that way in the Astleys Electrical wb with no problems. Also Quote is an exact match to the Sheet in this workbook.
I tried activating the workbook Astleys Electrical before setting the JobsSh too but I still get the error.

[p45cal]

I dont know how I did not see that. Thankyou however now I get the runtime error on the line where I set JobSh… …This sheet is in the Astleys Electrical workbook.

Set JobSh = Sheets("Jobs")

Then you'd need to qualify the workbook too, since if you don't, the workbook that the code-module's in will be the workbook it looks for a sheet called Jobs in, I think, so it may not find it. Perhaps change to:

Set JobSh = MasterWb.Sheets("Jobs")

[GrantAstley]

Yes I realised that after I posted and changed it to what you suggested still with no luck. I think it should also be

Set JobsSh = MasterWb.Worksheets("Jobs")

but that does not work either. I even tried

Workbooks("Astleys Electrical.xlsm").Sheets("Jobs").Range("I2") = Wb.Sheets("Quote").Range("H4")  'Input Quote No so spreadsheet can calc which row to be on

however this still throws the error 9. I think its defiantly to do with the "Jobs" part but I dont know why.

[p45cal]

Doubleclick on the tab of the Jobs sheet to highlight the full name of the sheet, press ctrl+C.
Then go to the code module, enter two double-quote marks (thus: "") and paste the clipboard contents between the 2 quote marks with ctrl+V. Does it look as you'd expect?
Do the same with the Quote sheet.

[GrantAstley]

Hi Yes that was the problem. There was a space after the Jobs so it was "Jobs " removed space and now it works.
One other problem which has occured now though. In line Rowx = MasterWb.JobsSh.Range("I3").Value now returnes an error 438 object does not support this property or method. Have also tried

Rowx = Application.WorksheetFunction.Match(Wb.Worksheets("Quote").Range("H4"), MasterWb.JobsSh.Range("B8:B1000"), 0)

but get the same error. I know this is a bit off topic and if you think I should start a new thread just let me know.
Thank you for your help.

[GrantAstley]

Dont worry all figured out. I should have been using just JobsSh instead of MasterWb.JobsSh
Thanx again.