working with excel97

What I am trying to acheive with the code below is that when I fill out my pricing backup from direct costs with all the markups and assoiated tabs, there is one tab called cover page. From time to time this needs to be emailed out with no associated links (just the cover page as well as not everyone being computer literate, needs to be acheived at the press of a button) and needs to be saved by another name.

I have it working as follows:

1) store full path name in variable

2) paste variable into a cell and copy it to the cell below (in this case "Backup" is in the existing file name in first cell and gets changed in second cell to "Memo" with the replace command. Then hides the two rows.

3) Copies required information from existing cover page.

4) Creates a new workbook.

5) Pastes it in the new workbook.

6) **This is where my problem starts**
It says when trying to save a file with a predetermined file name - "Subscripting out of range".
- What does this mean?
- What is the best way to handle saving a file with the name predetermined whether it is saving an existing file that is open or creating a new file?

I really hope someone can help me out and understand what I am trying to do!


[Public Sub NewWorkBook()
Dim objNewWorkBook As Object
Dim strFName As String
Dim strOriginalName As String
Dim strNewName As String

'This creates a new workbook that copies an existing worksheet with formulas
'to a new worksheet without formulas so it can be emailed out

'Takes activeworkbook file path and puts into a string
Let strOriginalName = ActiveWorkbook.FullName

'Workbooks(strOriginalName).Activate
Sheets("Change Order Cover Page").Select

Range("a1") = strOriginalName

Range("A1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False

'Setting up to change the file name that contains the word "Backup" to "Memo"
ActiveCell.Replace What:="Backup", Replacement:="Memo", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Cells.Find(What:="Backup", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate

Let strNewName = ActiveSheet.Range("a2")
ActiveSheet.Rows("1").Hidden = True
ActiveSheet.Rows("2").Hidden = True

'Copies all information from Original spread sheet
Sheets("Change Order Cover Page").Range("A3:F41").Copy

'Creates a new workbook
Set objNewWorkBook = Workbooks.Add

ActiveSheet.Range("a1").PasteSpecial Paste:=xlValues

'Save workbook with new file name
Workbooks(strNewName).Save


End Sub]