This is a small variation that allows the user to select the filename of the
new workbook.

Sub Test()
Dim NewWkb As Workbook
Dim OldWkb As Workbook
Dim sFileName As String
Dim fd As FileDialog

Set OldWkb = ActiveWorkbook

Set fd = Application.FileDialog(msoFileDialogSaveAs)

If fd.Show = -1 Then
sFileName = fd.SelectedItems(1)

Set NewWkb = Workbooks.Add()

NewWkb.SaveAs sFileName

OldWkb.Activate

OldWkb.Worksheets("One").Copy , NewWkb.Sheets("Sheet3")
'Will Copy after Sheet3 in New Workbook
'Copy additional sheets using same command as above
End If

Set NewWkb = Nothing
Set OldWkb = Nothing
Set fd = Nothing

End Sub

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


"natijoe" wrote in
message ...

Hi all,

I'm trying to come up with a macro that will add a new workbook and
copy certain sheets from the old workbook to the new one. However, I
also need to be able to prompt the user to name the new workbook. and
then have the macro know that the sheets from the old workbook go into
the newly named one.
I was able to come up with a macro that would do this but everytime i
ran the macro it would automatically call the added workbook "book1"
then "book2" and so on but the macro code still referred to it as "book
1" and would subsequently error out.

Can anybody offer any advice?

Thank you very much...


--
natijoe
------------------------------------------------------------------------
natijoe's Profile:
http://www.excelforum.com/member.php...o&userid=25556
View this thread: http://www.excelforum.com/showthread...hreadid=393074