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.

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.

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.
.

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

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.
.

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.
.

.

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.
.

.

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.

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.

.

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.

.