Macro to Copy Sheets to new workbook
in [General]
|
Prev: Why does sheet formatting change when pasting into a new book?
Next: Excel 2007 - Formatting text in cell (character by character)
From: KennyD on 30 Jan 2010 12:04 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" <KennyD(a)discussions.microsoft.com> wrote in message news:5882D1FD-2644-4162-B9AA-EF1DBD7C76C4(a)microsoft.com... > >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" <KennyD(a)discussions.microsoft.com> wrote in message news:D8291B4E-6983-4195-82E7-A643AD35206C(a)microsoft.com... > >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:31CC76FE-95C2-4593-8AC8-003799F591E7(a)microsoft.com... > >> >> > 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. > >> >> . > >> >> > >> . > >> > . >
From: Ron de Bruin on 30 Jan 2010 13:20 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" <KennyD(a)discussions.microsoft.com> wrote in message news:C108B546-3735-45A9-A636-DCBBD273F0EE(a)microsoft.com... > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:5882D1FD-2644-4162-B9AA-EF1DBD7C76C4(a)microsoft.com... >> >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" <KennyD(a)discussions.microsoft.com> wrote in message news:D8291B4E-6983-4195-82E7-A643AD35206C(a)microsoft.com... >> >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:31CC76FE-95C2-4593-8AC8-003799F591E7(a)microsoft.com... >> >> >> > 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. >> >> >> . >> >> >> >> >> . >> >> >> . >>
From: KennyD on 30 Jan 2010 15:01 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" <KennyD(a)discussions.microsoft.com> wrote in message news:C108B546-3735-45A9-A636-DCBBD273F0EE(a)microsoft.com... > > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:5882D1FD-2644-4162-B9AA-EF1DBD7C76C4(a)microsoft.com... > >> >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" <KennyD(a)discussions.microsoft.com> wrote in message news:D8291B4E-6983-4195-82E7-A643AD35206C(a)microsoft.com... > >> >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:31CC76FE-95C2-4593-8AC8-003799F591E7(a)microsoft.com... > >> >> >> > 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. > >> >> >> . > >> >> >> > >> >> . > >> >> > >> . > >> > . >
From: Ron de Bruin on 30 Jan 2010 16:29 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" <KennyD(a)discussions.microsoft.com> wrote in message news:87753E58-FE46-44F9-AF2E-A6E9DF826CFD(a)microsoft.com... > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:C108B546-3735-45A9-A636-DCBBD273F0EE(a)microsoft.com... >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:5882D1FD-2644-4162-B9AA-EF1DBD7C76C4(a)microsoft.com... >> >> >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" <KennyD(a)discussions.microsoft.com> wrote in message news:D8291B4E-6983-4195-82E7-A643AD35206C(a)microsoft.com... >> >> >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:31CC76FE-95C2-4593-8AC8-003799F591E7(a)microsoft.com... >> >> >> >> > 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. >> >> >> >> . >> >> >> >> >> >> >> . >> >> >> >> >> . >> >> >> . >>
From: Ron de Bruin on 30 Jan 2010 16:38
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" <rondebruin(a)kabelfoon.nl> wrote in message news:OV09JNfoKHA.4436(a)TK2MSFTNGP02.phx.gbl... > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:87753E58-FE46-44F9-AF2E-A6E9DF826CFD(a)microsoft.com... >> 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" <KennyD(a)discussions.microsoft.com> wrote in message news:C108B546-3735-45A9-A636-DCBBD273F0EE(a)microsoft.com... >>> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:5882D1FD-2644-4162-B9AA-EF1DBD7C76C4(a)microsoft.com... >>> >> >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" <KennyD(a)discussions.microsoft.com> wrote in message news:D8291B4E-6983-4195-82E7-A643AD35206C(a)microsoft.com... >>> >> >> > 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" <KennyD(a)discussions.microsoft.com> wrote in message news:31CC76FE-95C2-4593-8AC8-003799F591E7(a)microsoft.com... >>> >> >> >> > 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. >>> >> >> >> . >>> >> >> >> >>> >> >> . >>> >> >> >>> >> . >>> >> >>> . >>> |