Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

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?

Macro for Copying Worksheet to New Workbook
 

This should work for 1 sheet for all in the workbook. You just select the
sheet(s) to be copied before calling the macro. As before, copy from Sub ...
through End Sub and paste into a code module. It's slightly different than
previous, but the end effect is the same. In order to deal with multiple
selected sheets we have to deal with the ActiveWindow object rather than a
Workbook object. I probably could have coded it slightly different, but this
works just fine.

Sub CopySelectedSheetsToNewWorkbook()
'select the sheet(s) to be copied and
'then run this macro to copy them to
'a new workbook.
'works for 1 sheet or all in a workbook
'
Dim activeWB As String
Dim newWB As String

activeWB = ActiveWorkbook.Name
'create a new workbook
'it becomes the ActiveWorkbook
'turn off ScreenUpdating to keep from flickering
Application.ScreenUpdating = False
Workbooks.Add
newWB = ActiveWorkbook.Name
' back to this workbook
Workbooks(activeWB).Activate
ActiveWindow.SelectedSheets.Copy _
Befo=Workbooks(newWB).Sheets(1)
'now jump back to the new workbook for final operations
Workbooks(newWB).Activate
Application.ScreenUpdating = True
'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:

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?

Macro for Copying Worksheet to New Workbook
 

That's good to hear.

Trust me, it won't stay mastered! You're about to start learning that there
is no end to application development - someone will see it and look at you
and say 'hey, you know, couldn't we also get it to .....' and you'll be off
and running again <g.


"MGC" wrote:

J: This works perfectly!! Thanks again for all your help! This project has
been mastered and is so much easier now! Thanks again and have a great day!


"JLatham" wrote:

This should work for 1 sheet for all in the workbook. You just select the
sheet(s) to be copied before calling the macro. As before, copy from Sub ...
through End Sub and paste into a code module. It's slightly different than
previous, but the end effect is the same. In order to deal with multiple
selected sheets we have to deal with the ActiveWindow object rather than a
Workbook object. I probably could have coded it slightly different, but this
works just fine.

Sub CopySelectedSheetsToNewWorkbook()
'select the sheet(s) to be copied and
'then run this macro to copy them to
'a new workbook.
'works for 1 sheet or all in a workbook
'
Dim activeWB As String
Dim newWB As String

activeWB = ActiveWorkbook.Name
'create a new workbook
'it becomes the ActiveWorkbook
'turn off ScreenUpdating to keep from flickering
Application.ScreenUpdating = False
Workbooks.Add
newWB = ActiveWorkbook.Name
' back to this workbook
Workbooks(activeWB).Activate
ActiveWindow.SelectedSheets.Copy _
Befo=Workbooks(newWB).Sheets(1)
'now jump back to the new workbook for final operations
Workbooks(newWB).Activate
Application.ScreenUpdating = True
'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:

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?

Macro for Copying Worksheet to New Workbook
 

J: This works perfectly!! Thanks again for all your help! This project has
been mastered and is so much easier now! Thanks again and have a great day!


"JLatham" wrote:

This should work for 1 sheet for all in the workbook. You just select the
sheet(s) to be copied before calling the macro. As before, copy from Sub ...
through End Sub and paste into a code module. It's slightly different than
previous, but the end effect is the same. In order to deal with multiple
selected sheets we have to deal with the ActiveWindow object rather than a
Workbook object. I probably could have coded it slightly different, but this
works just fine.

Sub CopySelectedSheetsToNewWorkbook()
'select the sheet(s) to be copied and
'then run this macro to copy them to
'a new workbook.
'works for 1 sheet or all in a workbook
'
Dim activeWB As String
Dim newWB As String

activeWB = ActiveWorkbook.Name
'create a new workbook
'it becomes the ActiveWorkbook
'turn off ScreenUpdating to keep from flickering
Application.ScreenUpdating = False
Workbooks.Add
newWB = ActiveWorkbook.Name
' back to this workbook
Workbooks(activeWB).Activate
ActiveWindow.SelectedSheets.Copy _
Befo=Workbooks(newWB).Sheets(1)
'now jump back to the new workbook for final operations
Workbooks(newWB).Activate
Application.ScreenUpdating = True
'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:

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?