Dim bk as Workbook, sh as Worksheet
set sh = Workbooks("Book1").Sheets("Sheet 1")
set bk = Workbooks.Add
sh.Range("A1:A3").Copy _
Destination:=Activesheet.range("A1")
bk.SaveAs Filename:= _
sh.Range("A4").Value & ".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Windows("Book1").Activate

--
Regards,
Tom Ogilvy


"mwc0914" wrote in
message ...

I have my workbook open. I want to open a new workbook, copy the data
from my active sheet to the active sheet of the new workbook, then save
the new workbook with a file name that is the value in a cell on my
original active sheet. I understand that the new workbook will be saved
in the same directory where my original workbook is located.

When recording a macro, here is what I come up with. I think all I'm
missing is the file name parameter in the save portion (where I have
the ???).

Workbooks.Add
Windows("Book1").Activate
Sheets("Sheet 1").Select
Range("A1:A3").Select
Selection.Copy
Windows("Book2").Activate
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:= _
"???", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Windows("Book1").Activate


--
mwc0914
------------------------------------------------------------------------
mwc0914's Profile:
http://www.excelforum.com/member.php...o&userid=24130
View this thread: http://www.excelforum.com/showthread...hreadid=390203