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

Here is a little code to help you. Without seeing your code it is hard to be
too specific...

Sub CopyToNewBook()
Dim wbk As Workbook

Set wbk = Workbooks.Add

Sheet1.Copy wbk.Sheets(wbk.Sheets.Count)
wbk.Activate
Application.Dialogs(xlDialogSaveAs).Show "C:\MyBook.xls"

End Sub
--
HTH...

Jim Thomlinson


"natijoe" wrote:


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

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

Thanks Jim. That helped a lot.
And David, I was just going to ask the same question that you already
answered... Thank you very much.

Could you explain what the "fd as File Dialogue" does?

Thank you.


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

The FileDialog object allows you to access the same dialog boxes that Excel
gives you when you choose "Save As" or "Open" from the File Menu for
instance. The user can browse to a particular directory and choose a
filename. The new workbook is then saved to this directory and filename.

You can find more information on the FileDialog object he

http://support.microsoft.com/default...b;en-us;288543

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

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


"natijoe" wrote in
message ...

Thanks Jim. That helped a lot.
And David, I was just going to ask the same question that you already
answered... Thank you very much.

Could you explain what the "fd as File Dialogue" does?

Thank you.


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