|
Copy two sheets from workbook & create new workbook
Hello,
I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
Hi mike
See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
not tested but something like this maybe:
Dim Wkb As Workbook Dim FilePathName As String With Sheets("Data") FilePathName = .Range("I23") & "\" & _ .Range("I19") & "\" & _ .Range("I21") & "\" & _ Date$ & ".xls" End With 'sheets you want to copy '- change as required Sheets(Array("Sheet1", "Sheet2")).Copy Set Wkb = ActiveWorkbook With Wkb .SaveAs Filename:=FilePathName .Close False End With -- jb "Mike R." wrote: Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
Hi Ron,
For some reason, I am getting a "Subscript out of range" error on the Array line of code. I did change the Sheets to my actual sheets I want to copy. Help... Mike "Ron de Bruin" wrote: Hi mike See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
Show me the code Mike that you use now
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hi Ron, For some reason, I am getting a "Subscript out of range" error on the Array line of code. I did change the Sheets to my actual sheets I want to copy. Help... Mike "Ron de Bruin" wrote: Hi mike See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
Here it is... thanks Ron.
Sub aUpdate_Daily() Dim FilePathName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim TheActiveWindow As Window Dim TempWindow As Window With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook 'We add a temporary Window to avoid the Copy problem 'if there is a List or Table in one of the sheets and 'if the sheets are grouped With Sourcewb Set TheActiveWindow = ActiveWindow Set TempWindow = .NewWindow .Sheets(Array("Sheet10", "Sheet13")).Copy End With 'Close temporary Window TempWindow.Close Set Destwb = ActiveWorkbook ' 'Change all cells in the worksheets to values if you want For Each sh In Destwb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Destwb.Worksheets(1).Select Next sh 'Save the new workbook/Mail it/Delete it FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" With Destwb .SaveAs FilePathName .Close SaveChanges:=False End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Ron de Bruin" wrote: Show me the code Mike that you use now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hi Ron, For some reason, I am getting a "Subscript out of range" error on the Array line of code. I did change the Sheets to my actual sheets I want to copy. Help... Mike "Ron de Bruin" wrote: Hi mike See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
Hi Mike
The sheet names are not correct then Maybe there is a space before or after the sheet name Check this out -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Here it is... thanks Ron. Sub aUpdate_Daily() Dim FilePathName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim TheActiveWindow As Window Dim TempWindow As Window With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook 'We add a temporary Window to avoid the Copy problem 'if there is a List or Table in one of the sheets and 'if the sheets are grouped With Sourcewb Set TheActiveWindow = ActiveWindow Set TempWindow = .NewWindow .Sheets(Array("Sheet10", "Sheet13")).Copy End With 'Close temporary Window TempWindow.Close Set Destwb = ActiveWorkbook ' 'Change all cells in the worksheets to values if you want For Each sh In Destwb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Destwb.Worksheets(1).Select Next sh 'Save the new workbook/Mail it/Delete it FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" With Destwb .SaveAs FilePathName .Close SaveChanges:=False End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Ron de Bruin" wrote: Show me the code Mike that you use now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hi Ron, For some reason, I am getting a "Subscript out of range" error on the Array line of code. I did change the Sheets to my actual sheets I want to copy. Help... Mike "Ron de Bruin" wrote: Hi mike See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
Copy two sheets from workbook & create new workbook
got it... yep, I goofed the sheet names. Thanks Ron!
"Ron de Bruin" wrote: Hi Mike The sheet names are not correct then Maybe there is a space before or after the sheet name Check this out -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Here it is... thanks Ron. Sub aUpdate_Daily() Dim FilePathName As String Dim FileExtStr As String Dim FileFormatNum As Long Dim Sourcewb As Workbook Dim Destwb As Workbook Dim sh As Worksheet Dim TheActiveWindow As Window Dim TempWindow As Window With Application .ScreenUpdating = False .EnableEvents = False End With Set Sourcewb = ActiveWorkbook 'Copy the sheets to a new workbook 'We add a temporary Window to avoid the Copy problem 'if there is a List or Table in one of the sheets and 'if the sheets are grouped With Sourcewb Set TheActiveWindow = ActiveWindow Set TempWindow = .NewWindow .Sheets(Array("Sheet10", "Sheet13")).Copy End With 'Close temporary Window TempWindow.Close Set Destwb = ActiveWorkbook ' 'Change all cells in the worksheets to values if you want For Each sh In Destwb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Destwb.Worksheets(1).Select Next sh 'Save the new workbook/Mail it/Delete it FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" With Destwb .SaveAs FilePathName .Close SaveChanges:=False End With With Application .ScreenUpdating = True .EnableEvents = True End With End Sub "Ron de Bruin" wrote: Show me the code Mike that you use now -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hi Ron, For some reason, I am getting a "Subscript out of range" error on the Array line of code. I did change the Sheets to my actual sheets I want to copy. Help... Mike "Ron de Bruin" wrote: Hi mike See how i do it in this mail macro with a array http://www.rondebruin.nl/mail/folder1/mail3.htm I also create a temporary window to avoid a bug It is up to you if you want to use it -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Mike R." wrote in message ... Hello, I would like to copy two worksheets (formatting and all...) from one workbook and create a new workbook with just those two sheets. Here is the code I used to copy one worksheet, but I can not figure out how to do two. I am not opposed to all new coding: ' save worksheet with this file name FilePathName = Sheets("Data").Range("I23") & "\" & Sheets("Data").Range("I19") & "\" & Sheets("Data").Range("I21") & "\" & Date$ & ".xls" newsht = ActiveSheet.Copy Set Wkb = ActiveWorkbook Wkb.Sheets("Current Openings").Name = "Current Openings" With ActiveWorkbook .SaveAs Filename:=FilePathName .Close False End With Thank you in advance for your help! |
| All times are GMT +1. The time now is 03:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com