Renaming workbook in VBA & Sum function?

[ajmckenna]

I want to add a section to my macro to rename the worksheets created to the text in a particular cell (i.e. "2000 P&L" which is in cell A2 any ideas?

Also in using a 3d sum function to sum up multiple sheets, my workbook names may change is it possible to enter the formula using worksheet index e.g

current formula

=SUM(Sheet1:Sheet2!B7)to something like

=SUM(Worksheet(1):Worksheet(worksheet.count-1)!B7) this formula obviously doesn't word

I want it to sum the same cell in the first worksheet to the next to last worksheet
any ideas?

thanks all
AJ

 

[Joe Was]

This uses a hard coded save path, it could be made to pull the save-as box to select a folder if you need to. JSW

Sub SaveByCell()
Dim sFileName As String
'Cell A1 has the new file name.
sFileName = Sheets("Sheet1").Range("A1")

'If Cell A1 is Blank do not save.
If sFileName = "" Then Exit Sub

'The Drive:Path & the file name in cell A1.
ThisWorkbook.SaveAs "C:MyFilesJSWExcelData" & sFileName
End Sub