I have a workbook with 30 worksheets. One Master sheet and 29 report
sheets with initial name starting with 'R1' to 'R29' which contains
links,formulas,functions,modules.

I want to export all the 29 report sheets to a new workbook without the
links,formulas,functions,modules but just containing the values.I am
doing this for reducing file size.

How to do this in vba?Please help.

Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

After you copy the worksheets over into a new workbook,
highlight the cells with formulas, right click to copy and
right click again and choose paste special. In the top
section choose values and then ok. Essentially you are
copying the values right over the same cells where the
formulas were before. But now you have no formulas.

-----Original Message-----

I have a workbook with 30 worksheets. One Master sheet
and 29 report
sheets with initial name starting with 'R1' to 'R29'
which contains
links,formulas,functions,modules.

I want to export all the 29 report sheets to a new
workbook without the
links,formulas,functions,modules but just containing the
values.I am
doing this for reducing file size.

How to do this in vba?Please help.

Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Dim wkbk as Workbook
Dim fname as String

fname = Thisworkbook.Fullname
fname = Left(fname,len(fname)-4) & "_bak.xls"
Thisworkbook.SaveCopyas fname
set wkbk = workbooks.open( fname)

for each sh in wkbk.worksheets
sh.usedrange.formula = sh.usedrange.value
Next
Application.DisplayAlerts = False
wkbk.worksheets("Master").Delete
Application.DisplayAlerts = True
wkbk.close SaveChanges:=True

--
Regards,
Tom Ogilvy


"Michael168" wrote in message
...

I have a workbook with 30 worksheets. One Master sheet and 29 report
sheets with initial name starting with 'R1' to 'R29' which contains
links,formulas,functions,modules.

I want to export all the 29 report sheets to a new workbook without the
links,formulas,functions,modules but just containing the values.I am
doing this for reducing file size.

How to do this in vba?Please help.

Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Hi! Tom,

It works great except it still copy all the modules from the original
workbook to the new workbook. How can I get rid the modules being
copied to the new workbook?

Thank you.

Tom Ogilvy wrote:
*Dim wkbk as Workbook
Dim fname as String

fname = Thisworkbook.Fullname
fname = Left(fname,len(fname)-4) & "_bak.xls"
Thisworkbook.SaveCopyas fname
set wkbk = workbooks.open( fname)

for each sh in wkbk.worksheets
sh.usedrange.formula = sh.usedrange.value
Next
Application.DisplayAlerts = False
wkbk.worksheets("Master").Delete
Application.DisplayAlerts = True
wkbk.close SaveChanges:=True

--
Regards,
Tom Ogilvy


"Michael168" wrote in
message
...

I have a workbook with 30 worksheets. One Master sheet and 29
report
sheets with initial name starting with 'R1' to 'R29' which
contains
links,formulas,functions,modules.

I want to export all the 29 report sheets to a new workbook without
the
links,formulas,functions,modules but just containing the values.I
am
doing this for reducing file size.

How to do this in vba?Please help.

Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/
*


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Michael,

For removing modules, see Chip Pearson's site. He has a page on working
with VBE objects and some code for removing modules:

http://www.cpearson.com/excel/vbe.htm

hth,

Doug

"Michael168" wrote in message
...

Hi! Tom,

It works great except it still copy all the modules from the original
workbook to the new workbook. How can I get rid the modules being
copied to the new workbook?

Thank you.

Tom Ogilvy wrote:
*Dim wkbk as Workbook
Dim fname as String

fname = Thisworkbook.Fullname
fname = Left(fname,len(fname)-4) & "_bak.xls"
Thisworkbook.SaveCopyas fname
set wkbk = workbooks.open( fname)

for each sh in wkbk.worksheets
sh.usedrange.formula = sh.usedrange.value
Next
Application.DisplayAlerts = False
wkbk.worksheets("Master").Delete
Application.DisplayAlerts = True
wkbk.close SaveChanges:=True

--
Regards,
Tom Ogilvy


"Michael168" wrote in
message
...

I have a workbook with 30 worksheets. One Master sheet and 29
report
sheets with initial name starting with 'R1' to 'R29' which
contains
links,formulas,functions,modules.

I want to export all the 29 report sheets to a new workbook without
the
links,formulas,functions,modules but just containing the values.I
am
doing this for reducing file size.

How to do this in vba?Please help.

Thanks.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/
*


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/