PDA

View Full Version : [SOLVED] Naming a Workbook from a cell



rosspmm
06-29-2005, 06:39 AM
I have a report that is users need to complete on a quarterly basis - the report is in an excel workbook.

I have some VBA code assigned to a button to carry out some conditional formatting and to save the workbook to a central directory; all I now need is to be able to auto name the saved file using the contents of 2 cells that are always in the same place (C4 & C5) - the resulting file name needs to be "C4 + C5".xls

the code I am using to carry out the save is:



Sub SaveCopy()
Dim FName As Variant
FName = Application.GetSaveAsFilename
If FName <> False Then
If FName <> ActiveWorkbook.Name Then
On Error GoTo Error_SaveAs
ActiveWorkbook.SaveAs Filename:=FName
Else
ActiveWorkbook.Save
End If
End If
Exit Sub
Error_SaveAs:
MsgBox "error executing SaveAs with filename = " & FName & vbCrLf & _
"no save done", vbCritical
End Sub


I would also like to carry this out without the User seeing the dialogue box.

All help gratefully appreciated as I just cant figure it out :banghead:

Jacob Hilderbrand
06-29-2005, 07:05 AM
FName = Sheets("Sheet1").Range("C4").Text & Sheets("Sheet1").Range("C4").Text & ".xls"
ActiveWorkbook.SaveAs Filename:=FName

rosspmm
06-29-2005, 07:22 AM
Ok what am I doing wrong?

Inserted your lines of code but now I get a "subscript out of range" error on the FName line (I changed it to sheet 2 as that is the sheet with the text I need to use in)



Dim FName As Variant
FName = Sheets("Sheet2").Range("C4").Text & Sheets("Sheet2").Range("C5").Text & ".xls"
If FName <> False Then
If FName <> ActiveWorkbook.Name Then
On Error GoTo Error_SaveAs
ActiveWorkbook.SaveAs Filename:=FName
Else
ActiveWorkbook.Save
End If
End If
Exit Sub
Error_SaveAs:
MsgBox "error executing SaveAs with filename = " & FName & vbCrLf & _
"no save done", vbCritical
End Sub

Norie
06-29-2005, 07:36 AM
Is Sheet2 definitely the exact name of the sheet?

Is the workbook with that sheet active?

rosspmm
06-29-2005, 07:40 AM
:doh: Of course

Having named the sheets properly in the code it now works

Many thanks to all:beerchug: