Run-time error '9': Subscript out of range error when adding a new file

[Minnesota Mike]

This is my first post as a new member. I have read a great many previous posts in the past and found the information to be very helpful. However, this time I have not been able to find an answer to solve my problem.

My problem is that I get the Run Time Error 9 after adding a new file and saving that file. The next piece of code after the SaveAs errors out on me and I can't figure out why after trying several alternatives. I am using Excel 2010.

Here is the relevant section of code:


Set Newbook = Workbooks.Add(1)

With Newbook
If Val(Application.Version) < 12 Then
'You are using Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You are using Excel 2007-2013
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End With

TempFilePath = "I:\Finance\FINANCE\Users\Eric\BMR Stats\BMR_" & YEARYY & "\"
TempFileName = "StatisticsEUR_" & DateMMYY

With Newbook
.Title = "All Stats"
.Subject = "Stats"
.SaveAs Filename:="I:\Finance\FINANCE\Users\Eric\BMR Stats\BMR_" & YEARYY & "\StatisticsEUR_" & DateMMYY & ".xls"
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
Workbooks(TempFileName).Worksheets("Sheet1").Activate
End With


The macro stops at the second to last line "Workbooks(TempFileName).Worksheets("Sheet1").Activate" when I use the Debugger. I have tried to Activate the file, Select the file, even Activate a different file. I have tried to Activate the file outside the With/End With commands. The macro always stops at what ever comes after the SaveAs command. The SaveAs step seems to work fine; the added workbook is saved in the specified location with the specified name.

Could it have something to do with run running the macro from a file in one directory and saving the added file to a different directory? Do I need to be more specific about stating the path? (I am thinking not because both files are already open.)

Thanks for your assistance and guidance.

 

[ParamRay]

Change the line to:

Workbooks(TempFileName & FileExtStr).Worksheets("Sheet1").Activate

 

[Andrew Poulsom]

Welcome to MrExcel.

Can't use?

.Worksheets("Sheet1").Activate

 

[Minnesota Mike]

Thanks. That was the difference. I got hung up thinking that I was already including the "file name" and forgot I had the extra step to get the proper extension attached to the file name.