I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

Also, there may be times (although few) when I will need to pull two sheets
to a new workbook...can this be done via a macro?

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

I cannot get this one to work...which part of this should I be copying into
VBA?

"JLatham" wrote:

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

Works fine for me.

Copy all of it from Sub CopyActiveSheetToNewWorkbook() to End Sub into a
general module in your workbook.


Gord Dibben MS Excel MVP

On Wed, 15 Aug 2007 16:16:01 -0700, MGC wrote:

I cannot get this one to work...which part of this should I be copying into
VBA?

"JLatham" wrote:

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

Thanks - good to know that I'm not the only one it works for.

MGC - if you need more detailed 'install' instructions:
open your workbook, press [Alt]+[F11] to enter the VBA module. If there's a
'general' code module available, just copy and paste into it. If in doubt,
use Insert | Module from the VB Editor's menu and then just copy and paste
into the new module presented. Doesn't hurt anything to have multiple
general code modules in the file.

"Gord Dibben" wrote:

Works fine for me.

Copy all of it from Sub CopyActiveSheetToNewWorkbook() to End Sub into a
general module in your workbook.


Gord Dibben MS Excel MVP

On Wed, 15 Aug 2007 16:16:01 -0700, MGC wrote:

I cannot get this one to work...which part of this should I be copying into
VBA?

"JLatham" wrote:

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

Gord/J:

Thank you for your help! I wasn't sure exactly what to copy when I saw
'JLatham' about halfway through. Everything works great! Thanks again for
all your help!

"JLatham" wrote:

Thanks - good to know that I'm not the only one it works for.

MGC - if you need more detailed 'install' instructions:
open your workbook, press [Alt]+[F11] to enter the VBA module. If there's a
'general' code module available, just copy and paste into it. If in doubt,
use Insert | Module from the VB Editor's menu and then just copy and paste
into the new module presented. Doesn't hurt anything to have multiple
general code modules in the file.

"Gord Dibben" wrote:

Works fine for me.

Copy all of it from Sub CopyActiveSheetToNewWorkbook() to End Sub into a
general module in your workbook.


Gord Dibben MS Excel MVP

On Wed, 15 Aug 2007 16:16:01 -0700, MGC wrote:

I cannot get this one to work...which part of this should I be copying into
VBA?

"JLatham" wrote:

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?

J/Gord:

Can this code be modified so that if I have two or three tabs which need
moved, I can move them at the same time?

This will not happen very often but is bound to occur. I guess what would
be ideal is if the code would allow me to grab a single sheet, or two, or all
three on an as needed basis. Can this be done?

"JLatham" wrote:

Thanks - good to know that I'm not the only one it works for.

MGC - if you need more detailed 'install' instructions:
open your workbook, press [Alt]+[F11] to enter the VBA module. If there's a
'general' code module available, just copy and paste into it. If in doubt,
use Insert | Module from the VB Editor's menu and then just copy and paste
into the new module presented. Doesn't hurt anything to have multiple
general code modules in the file.

"Gord Dibben" wrote:

Works fine for me.

Copy all of it from Sub CopyActiveSheetToNewWorkbook() to End Sub into a
general module in your workbook.


Gord Dibben MS Excel MVP

On Wed, 15 Aug 2007 16:16:01 -0700, MGC wrote:

I cannot get this one to work...which part of this should I be copying into
VBA?

"JLatham" wrote:

Perhaps this will help:

Sub CopyActiveSheetToNewWorkbook()
'copies the current active sheet
'in the active workbook to a
'new workbook and brings up the
'SaveAs dialog to finish the operation
'and closes the new workbook after the save
'
'the sheet to be copied doesn't even have to
'be in this workbook - you can have the book
'with this code in it open, open another workbook
'choose a sheet in it and then use
'Tools | Macro | Macros to pick this code
'
'jlatham 8/14/2007:
'email [remove spaces] - HelpFrom @ jlatham site.com
Dim activeWB As String
Dim thisSheet As String

activeWB = ActiveWorkbook.Name
thisSheet = Workbooks(activeWB).ActiveSheet.Name
'create a new workbook
'it becomes the ActiveWorkbook
Workbooks.Add
Workbooks(activeWB).Sheets(thisSheet).Copy _
Befo=ActiveWorkbook.Sheets(1)
'open save As dialog
Application.Dialogs(xlDialogSaveAs).Show
'new file has been saved, close it
'returning you to the original book
'
ActiveWorkbook.Close
End Sub

"MGC" wrote:

I am looking for a macro to copy 1 worksheet from a workbook to a new
workbook. I would like to take this as far as the "save as" part so that the
user only needs to select it's destination. I will be adding this to
existing VBA code. Can this be done and how?