Creating Report

[brownti via OfficeKB.com]

I am trying to create a macro that will create a report from the current open
file. i want it to create a new workbook and save it as the contents of a
cell in the current workbook. Here is the very rough beginings of the macro,
but i dont know how to get it to do what i want.

Sub report()
Workbooks.Add
ChDir "C:\Documents and Settings\tim\Desktop"
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\tim\Desktop\Book1.xls", FileFormat:
=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("B4").Select
ActiveCell.FormulaR1C1 = "=[Book2]Sheet1!R1C1"
Range("B5").Select
End Sub

 

[Tom Ogilvy]

Sub report()
Dim bk as Workbook
Dim sname as String
set bk = Activeworkbook
sName = Range("B9").Value
if sname = "" then exit sub
Workbooks.Add
ActiveWorkbook.SaveAs Filename:= _
"C:\Documents and Settings\tim\Desktop\" & _
sname & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Range("B4").Select
' linking formula back to the original workbook?
ActiveCell.FormulaR1C1 = "=[" & _
bk.name & "]Sheet1!R1C1"
Range("B5").Select
End Sub