|
Macro to Copy Sheets to new workbook
Found this macro by Dave Peterson and want to modify it so that I can do the
following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. |
Macro to Copy Sheets to new workbook
See
http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. |
Macro to Copy Sheets to new workbook
Funny thing, Ron, is that i just found your website from another post, and
was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . |
Macro to Copy Sheets to new workbook
Maybe you could just copy|paste special|values, too.
(Untested, uncompiled) Option explicit sub NewWorksheet() dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .cells.copy .cells.pastespecial paste:=xlpastevalues .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub KennyD wrote: Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . -- Dave Peterson |
Macro to Copy Sheets to new workbook
That is not what the code Dave posted or my code example do
Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . |
Macro to Copy Sheets to new workbook
I have a workbook that I have several macro's in, along with some large data
files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . |
Macro to Copy Sheets to new workbook
Your version produces a Run Time Error 7: Out of Memory error.
-- Nothing in life is ever easy - just get used to that fact. "Don Guillett" wrote: Try it this way Option Explicit Sub NewWorksheet() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets With wks .Cells.Value = Cells.Value .Copy 'to a new workbook .Parent.SaveAs Filename:="C:\temp\" & .Name & ".xls", _ FileFormat:=xlWorkbookNormal ActiveWorkbook.Close savechanges:=False End With Next wks ActiveWindow.Close savechanges:=False End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . |
Macro to Copy Sheets to new workbook
It was tested using two SELECTED sheets. -- Don Guillett Microsoft MVP Excel SalesAid Software "KennyD" wrote in message ... Your version produces a Run Time Error 7: Out of Memory error. -- Nothing in life is ever easy - just get used to that fact. "Don Guillett" wrote: Try it this way Option Explicit Sub NewWorksheet() Dim wks As Worksheet For Each wks In ActiveWindow.SelectedSheets With wks .Cells.Value = Cells.Value .Copy 'to a new workbook .Parent.SaveAs Filename:="C:\temp\" & .Name & ".xls", _ FileFormat:=xlWorkbookNormal ActiveWorkbook.Close savechanges:=False End With Next wks ActiveWindow.Close savechanges:=False End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . |
Macro to Copy Sheets to new workbook
hi Kenny
What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . |
Macro to Copy Sheets to new workbook
These are the sheets I do NOT want in the new workbook:
"Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . |
Macro to Copy Sheets to new workbook
I must know something else to
Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . |
Macro to Copy Sheets to new workbook
No - don't need the code modules in the new workbook. Only need the
formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . |
Macro to Copy Sheets to new workbook
Try this basic tester
Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . |
Macro to Copy Sheets to new workbook
Oops, change it to
Sub Test_Me_2() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this basic tester Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . |
Macro to Copy Sheets to new workbook
I think we're almost there. It copies everything over, but then I get a
runtime error 1004. Also, it copies over the formulas too. I'm going to try and see if I can't tweak it a little. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: Oops, change it to Sub Test_Me_2() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this basic tester Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . . |
Macro to Copy Sheets to new workbook
The error message that I get is 'Run Time Error 1004: Method 'Select' of
Object '_Worksheet' failed -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: Oops, change it to Sub Test_Me_2() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this basic tester Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . . |
Macro to Copy Sheets to new workbook
Then you have hidden worksheets in your workbook
Do you want to copy them also into the new workbook ? Or make values of the formulas if you want them in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... The error message that I get is 'Run Time Error 1004: Method 'Select' of Object '_Worksheet' failed -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: Oops, change it to Sub Test_Me_2() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this basic tester Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . . |
Macro to Copy Sheets to new workbook
Ron,
I started thinking about this and found a solution based on the code that you provided here. You attempted to copy the sheets to a new workbook, then delete the un-needed sheets and then copy and paste the values. What I did was to copy and paste the values on the visible sheets, and then copy the sheets to a new workbook and delete the un-needed sheets. Now all I need to do is include the Autosave funtion, and I'll be set. Thank you so much for your help. I really appreciate it. Who knows, maybe you can include this mode in your RDBMerge Add-in. :) Here is a copy of the code. Option Explicit Sub ExportActiveSheets () Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet 'Change all cells in the worksheet to values for all visible worksheets Set AWb = ActiveWorkbook For Each sh In AWb.Worksheets 'If the sheet is visible then copy it on to itself If sh.Visible = -1 Then sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells.Select End With Application.CutCopyMode = False End If Range("A1").Activate Next sh Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: Oops, change it to Sub Test_Me_2() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... Try this basic tester Sub Test_Me() Dim AWb As Workbook Dim NewWb As Workbook Dim N As Long Dim Shname As Variant Dim sh As Worksheet Set AWb = ActiveWorkbook Set NewWb = Workbooks.Add(1) NewWb.Sheets(1).Name = "qwertyuiop" AWb.Worksheets.Copy After:=NewWb.Worksheets(1) Shname = Array("qwertyuiop", "Combined", "Month1&2_Resid_Details", _ "Month3&4_Resid_Details", "Month5&6_Resid_Details", "Sheet_2", "Sheet1") Application.DisplayAlerts = False For N = LBound(Shname) To UBound(Shname) On Error Resume Next NewWb.Sheets(Shname(N)).Delete On Error GoTo 0 Next N Application.DisplayAlerts = True 'make values part For Each sh In NewWb.Worksheets sh.Select With sh.UsedRange .Cells.Copy .Cells.PasteSpecial xlPasteValues .Cells(1).Select End With Application.CutCopyMode = False Next sh End Sub -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... No - don't need the code modules in the new workbook. Only need the formatting, values and hyperlinks. No code modules or formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: I must know something else to Do you want to have the code modules also in the new workbook -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... These are the sheets I do NOT want in the new workbook: "Combined" "Month1&2_Resid_Details" "Month3&4_Resid_Details" "Month5&6_Resid_Details" "Sheet_2" "Sheet1" The sheets that I DO want in the new workbook will always have different names. But the first sheet will always be "SummarySheet". -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: hi Kenny What are the names of the sheet that you not want in the new workbook ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... I have a workbook that I have several macro's in, along with some large data files. Two of the macros create new sheets within this same workbook and then creates a summary sheet within this same workbook. I need to take the newly created sheets and summary sheet and copy them to a single new workbook, preferrably with the same sheet names, formatting, hyperlinks, but NOT the formulas. Your macro copies the selected sheets each to their own workbook. I can use that macro if there is a macro that I can use to then bring all the workbooks into a single workbook. I tried your RDBMerge add-in, but it just brings in all of the data. I need the individual sheets brought in. So, if there are ten sheets out of twenty that I export, I need to end up with a single workbook that has the ten sheets in it with all of the same formatting, hyperlinks and values but not the formulas. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: That is not what the code Dave posted or my code example do Please give more info -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Funny thing, Ron, is that i just found your website from another post, and was actually implementing your code. However, I don't know how to adjust. Specifically, I would like to export all of the sheets into 1 workbook. Your code exports them all to individual workbooks. Additionally, I would like to specify the folder and name of the workbook before I save it. How can I adjust your code to make that happen? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Ron de Bruin" wrote: See http://www.rondebruin.nl/copy6.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "KennyD" wrote in message ... Found this macro by Dave Peterson and want to modify it so that I can do the following: Select the ActiveSheets and Copy them to a new workbook - BUT I do not want to copy the formulas. Only want to copy the values, formatting (row height and column width), sheet names, hyperlinks. But NOT the formulas. The formulas that do all of the lookups are pretty intense and the copies just need to display the information in the nice pretty way I have it set up along with the hyperlinks between the sheets. At any rate, here's the original macro: Option explicit sub NewWorksheet dim wks as worksheet for each wks in activewindow.selectedsheets wks.copy 'to a new workbook with activesheet .parent.saveas filename:="C:\temp\" & .name & ".xls", _ fileformat:=xlworkbooknormal .parent.close savechanges:=false end with next wks end sub -- Nothing in life is ever easy - just get used to that fact. . . . . . |
| All times are GMT +1. The time now is 05:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com