Macro - Summing a Range and use the Range as the File Name

[shhykk]

Hi Experts,

I have pull together a set up codes, but the last bits is not working....

What the codes try to do:
1. Replace any blank cells in Column C with "OFFICE" (this part the macro is completed)
2. Find word "Group Totals" in the spreadsheet (this part the macro is completed)
3. In the same row as the "Group Totals", sum Column I to N. (Getting stuck)
4. The sum between Column I to N is then used as the file name (this part of the macro is completed)

Any help is appreciated

Kind regards,
Shhykk

Here's the code:-

Sub Macq_CMT()
'
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long




Dim fileName As String
Dim TotalName As String
Dim myRange As Range


Dim iTotal As Long


Set wks = ActiveSheet
With wks

col = .Range("C8").Column


Set rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = Nothing
On Error Resume Next
Set rng = .Range(.Cells(8, col), .Cells(LastRow, col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0


If rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
rng.FormulaR1C1 = "OFFICE"
End If






End With


Columns("C:C").Select
iTotal = Selection.Find(What:="GROUP TOTALS (Disk) = ", After:=ActiveCell).Row



myRange = ActiveSheet.Range(Cells(ActiveCell.Row, 9), Cells(ActiveCell.Row, 14)).Select
TotalName = WorksheetFunction.Sum(myRange)





ChDir "D:\Documents and Settings\wongph\Desktop\My Download"
ActiveWorkbook.SaveAs fileName:= _
"D:\Documents and Settings\wongph\Desktop\My Download\CMT $" & WorksheetFunction.Text(TotalName, "#,###.00") & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False








End Sub