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 17:47 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" <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. > >>> >> >> >> . > >>> >> >> >> > >>> >> >> . > >>> >> >> > >>> >> . > >>> >> > >>> . > >>> > > . >
From: KennyD on 30 Jan 2010 18:03 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" <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. > >>> >> >> >> . > >>> >> >> >> > >>> >> >> . > >>> >> >> > >>> >> . > >>> >> > >>> . > >>> > > . >
From: Ron de Bruin on 31 Jan 2010 04:41 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" <KennyD(a)discussions.microsoft.com> wrote in message news:B522E4C9-DCB6-46CD-8DB4-94AF25E4D4A4(a)microsoft.com... > 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" <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. >> >>> >> >> >> . >> >>> >> >> >> >> >>> >> >> . >> >>> >> >> >> >>> >> . >> >>> >> >> >>> . >> >>> >> >> . >>
From: KennyD on 31 Jan 2010 08:25
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" <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. > >>> >> >> >> . > >>> >> >> >> > >>> >> >> . > >>> >> >> > >>> >> . > >>> >> > >>> . > >>> > > . > |