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: Ron de Bruin on 29 Jan 2010 15:41 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 29 Jan 2010 17:02 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 29 Jan 2010 17:09 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 > dguillett(a)gmail.com > "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: Don Guillett on 29 Jan 2010 18:06 It was tested using two SELECTED sheets. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "KennyD" <KennyD(a)discussions.microsoft.com> wrote in message news:580DCC0F-DE34-4CEB-94D3-738E43B77366(a)microsoft.com... > 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 >> dguillett(a)gmail.com >> "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 05:08
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. >> >> . >> >> >> . >> |