Subscript out of range (error 9)

[renigar]

Hi All,
The procedure listed below gives me the above error which I am sure must be an easy fix but my very limited knowledge can't seem to figure it out. I swear this was working when I first made it but now I get the error at the indicated (by asterisks) line of code. If any one can point me in the right direction it would be most appreciated.

A little background, I work in a utility . We have programmable logic controllers that operate stations and send data back to a sequel server for collection. On the server we have an excel spreadsheet app that displays all the stations reads as of 7 AM. Some stations are not online and are done manually. A different macro is run daily after the manual update and populates a monthly log (each station has it's own sheet) in a format the operators are familiar with. The macro below grabs the end of month reads from the previous month's file and paste them to the FWD row of the current month's file. I hope I've given enough info.

Sub Update_FWD()
'
' Update_FWD Macro
' Macro recorded 4/8/2003
'
' Current month's file is already open
Worksheets("Update_FWD_Reads&quot.Activate
'
' Declare Variables
'
Dim strCurrentMonth As String
Dim strLastMonthFile As String
Dim strLastMonth As String
' -----------------------------------------------------------------
' Assign values to variables
'
' The following variable is the current month file with
' the path, hidden cell K5 on the Update_FWD_Reads
' worksheet calculates the value of the current month
strCurrentMonth = "C:\Data\MonthlyStationLogs\" & Format([K5], "mmm-yyyy&quot & ".xls"
' The following variable is a path to a file named with
' the month previous to the one reported in cell K5,
' ex. Aug-2002.xls
' Hidden cell K7 on the Update_FWD_Reads worksheet
' calculates this value
strLastMonthFile = "C:\Data\MonthlyStationLogs\" & Format([K7], "mmm-yyyy&quot & ".xls"
' The following variable is the previous month file
' without the path
strLastMonth = Format([K7], "mmm-yyyy&quot & ".xls"
' ---------------------------------------------------------
'
' Open last month's file and copy the reads from the
' last day of all sheets
Workbooks.Open (strLastMonthFile)
Sheets(Array("S-2", "S-4", "S-5", "S-6", "S-7", "S-8", "S-9", "S-10", "S-11", "S-12&quot). _
Select
Sheets("S-2&quot.Activate
Range("C38:N38&quot.Copy
' Paste last month's reads to current month's FWD row
*** Windows(strCurrentMonth).Activate ***
Sheets(Array("S-2", "S-4", "S-5", "S-6", "S-7", "S-8", "S-9", "S-10", "S-11", "S-12&quot). _
Select
Range("C6:N6&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("A1&quot.Select
Sheets("S-2&quot.Select
Range("A1&quot.Select
Workbook.Activate (strCurrentMonth)
ActiveWorkbook.Save
'
' Close last month's file
Windows(strLastMonth).Activate
Sheets("S-2&quot.Select
Range("A1&quot.Select
ActiveWindow.Close
' Current month’s file is left updated and open

End Sub

 

[BrainB]

1. I suggest you put in a line :-
MsgBox(strCurrentMonth)

to check that the string is what you expect and that it matches the workbook name.

My guess is that you have set Windows to *not* show the file suffix - so '.xls' does not appear in the window caption although your variable does include it.

2. Your code could not have completed correctly with the line :- <<Workbook.Activate (strCurrentMonth)>> which should read :-
WorkBooks(strCurrentMonth).Activate



Regards
BrianB
** Let us know if you get something that works !
================================

 

[TonyJollans]

Hi renigar,

The Window Title will not usually contain the path of your worksheet, but ...

Windows are for Users to look at and there might be more than one on the same Workbook. You want to do things explicitly to Workbooks so why not work with the Workbooks directly in your code, rather than Windows.

Enjoy,
Tony