Run-time error '9': Subscript out of range (With Statement)

[mariocubed]

Hey All,

I am trying to run a macro that will update a sheet in the workbook and post it in another location. I did not build this macro and have limited work with macros compared to the person who built it. Running the macro gives me a Run-time error '9' error within a With statement. Any advice would be much appreciated.

Please Login or Register  to view this content.

The directories this code references have not changed since the last running of the macro, so I do not believe that would be the issue.

Thanks again!

[Solus Rankin]

What line does the error occur on?

[mariocubed]

The error occurs within the first the With statement after the Bye.

With Workbooks(strReportFileName).Sheets(strReportSheetName)
.Range("E6") = "All-up"
.Range("E7") = "All-up"
.Range("E8") = "All-up"
End With
Workbooks(strReportFileName).Sheets(strPostingSheetName).Select
Range("A2").Select

[Solus Rankin]

are you sure strReportFileName and strReportSheetName are what you are expecting?

try debug.print strReportFileName & " " & strReportSheetName right before your With Workbooks line and see what value the variable holds. Is the workbook by that name open?

[swade730]

try activating the workbook/sheet name, then do the with statement

[Solus Rankin]

If the variables hold the expected value, since they are fully qualified there should be no need to activate them.

I'm thinking there is probably a variable that doesn't hold the value you think it does. Is there anyway you can attach your workbook as an example (with code in place) so we can do some investigating?

[mariocubed]

Here is a copy of the workbook with all internal data removed

[Solus Rankin]

The variable is not holding the correct information. I placed a message box here:

Please Login or Register  to view this content.

to show what information the variable held. If you run this it shows that strReportFileName hold the value:
"Microsoft Excel - test%20report[1].xlsm [Re" Because of the added characters on the trailing end this is not a valid workbook name, therefore it will give you an out of range error.