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 29 Jan 2010 13:52 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 29 Jan 2010 14:10 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 15:04 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: Dave Peterson on 29 Jan 2010 15:19 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" <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. > > . > > -- Dave Peterson
From: Don Guillett on 29 Jan 2010 15:35
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. |