Copying Worksheet - Run-time error '9'

[blizzardice]

Hello,

I am working with Excel 2003 on Windows XP. I have a master workbook with 14 different worksheets and I am trying to create 7 different workbooks from it; two sheets per workbook.


To give you an idea of what I'm trying to do, I've outlined 8 of the worksheets below:

• Equipment Workbook: comprised of "Current Equipment" and "Lst Yr Equipment" Worksheets
• Kits Workbook: comprised of "Current Kits" and "Lst Yr Kits" Worksheets
• Publications Workbook: comprised of "Current Publications" and "Lst Yr Publications Worksheets"
• Training Workbook: comprised of "Current Training" and "Lst Yr Training" Worksheets

I've written a code that creates a new workbook by copying and saving a specific Current worksheet and then copying the Lst Yr worksheet into the new workbook. It works for all the new workbooks, except the Training Workbook, which is by far the largest one.

The code I'm trying to use is below. I have bolded the highlighted line when the Run-time error '9': Subscript out of range error pops up.

Sub Training()
 
Dim FileName As String
Dim CurMonth, PrevMonth As String
Dim MonthNum, LstMonthNum, Year As Long
 
FileName = ActiveWorkbook.Name
 
Workbooks("Formatting.xls").Worksheets("output").Activate
 
'Setting Year Variable
Range("M2").Copy
With Range("T2")
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    .NumberFormat = "yyyy"
    .Value = Range("T2").Text
End With
Year = Right(Range("T2").Text, 2)
Range("T2").Clear
Worksheets("Current Data").Activate
 
'Setting Month Variables
CurMonth = Range("C2").Text
Worksheets("Months").Activate
Range("A3").Select
Do Until PrevMonth = CurMonth
        PrevMonth = ActiveCell.Value
        ActiveCell.Offset(1, 0).Select
Loop
MonthNum = ActiveCell.Offset(-1, 1).Value
Worksheets("Current Data").Activate
 
'Saving New Workbook
If MonthNum < 10 Then
[B]   Workbooks(FileName).Sheets("Current Training").Copy[/B]
        ActiveWorkbook.SaveAs FileName:= _
            "C:\Monthly CSC Reports\20" & Year & " 0" & MonthNum & "\MASTER\" & Left(FileName, 26) & "Rolling Training Avg CSC Info.xls" _
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
    Workbooks(FileName).Sheets("Lst Yr Training").Copy Before:=Workbooks(Left(FileName, 26) & "Rolling Training Avg CSC Info.xls").Sheets("Current Training")
Else: Workbooks(FileName).Sheets("Current Training").Copy
        ActiveWorkbook.SaveAs FileName:= _
            "C:\Monthly CSC Reports\20" & Year & " " & MonthNum & "\MASTER\" & Left(FileName, 26) & "Rolling Training Avg CSC Info.xls" _
            , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
            ReadOnlyRecommended:=False, CreateBackup:=False
    Workbooks(FileName).Sheets("Lst Yr Training").Copy After:=Workbooks(Left(FileName, 26) & "Rolling Training Avg CSC Info.xls").Sheets("Current Training")
End If
 
End Sub

I've quadruple-checked the FileName to make sure it's right (through a watch), as well as the spelling of the sheet. My added confusion is in the fact that this code works for all the other workbooks I need to create (I just replace Training with Equipment or Kits or etc), but it just refuses to work with the Training one.

I hope this is clear and would appreciate any insight anyone can give.

Thanks,
-Liz

 

[syntaxed]

What I think is happening is that:

Worksheets("Current Data").Activate

activates the worksheet named "Current Data" in the workbook from which you run the macro and whose name is passed to the FileName variable. Then this workbook remains activated until:

Workbooks(FileName).Sheets("Current Training").Copy

Then Excel 2003 throws an error because the active workbook is the same as Workbooks(FileName).

 

[Andrew Poulsom]

Either there isn't a workbook with the name you have assigned to FileName or that workbook doesn't contain a sheet named "Current Training". So check again.

 

[blizzardice]

=[UPDATE]=

Got it. Andrew was right. My Current Training worksheet had a space after the name. Thanks for the help! Greatly, greatly appreciated.






Thanks for all your input thus far.

@Andrew:

Either there isn't a workbook with the name you have assigned to FileName or that workbook doesn't contain a sheet named "Current Training". So check again.

I've copied and pasted the value of FileName and the name of the open workbook it refers to onto Notepad, one on top of the other, to make sure that the spelling and spaces were all aligned and correct. I am also positive the worksheet "Current Training" exists. The code seems to work for all the other sheets except Training, which is what has me so stumped.

@syntaxed:

Through a watch, I've made sure that the FileName value doesn't change between

Worksheets("Current Data").Activate

and

Workbooks(FileName).Sheets("Current Training").Copy

.

The first code refers to the workbook from which the macro is run ("Formatted.xls"), while the second refers to the FileName workbook. Again, the code works fine when I apply it to the other worksheets, just not Current Training.

----

I have narrowed down the problem to the following bolded code:

Workbooks(FileName).[B]Sheets("Current Training")[/B].Copy

Is there any reason why a sheet cannot be selected even if it exists?