Copy Worksheet Help?

  • Thread starter Thread starter Michael168
  • Start date Start date
M

Michael168

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