I would like to create a macro button that forces department managers (over 120) to save a monthly variance Excel file using a prescribed naming convention based on cells in the file but allow them to choose the folder in which they save the file.
I have wasted 6 hours trying to find ways to open a folder browser to replace my "C:\Documents and Settings\compope\Desktop\" with the folder managers are free to choose.
I would appreciate any help as I'm just learning how to use VBA.
Sub SaveFileWDeptName()
'
' SaveFileWDeptName Macro
' Macro recorded 10/24/2007 by compope
'
'
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\compope\Desktop\" & Sheet1.Range("C6").Value & "_" & Sheet1.Range("B1").Value & "_" & Sheet1.Range("B2").Value & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Range("B1").Select
End Sub