Copy all worksheets to new workbook as value

[Gerlof]

Hi everyone,

I read a thread on this forum that has a code that will copy all worksheets to a new workbook as values only. I tried using this code but I got a 'run-time error 9' - Subscript out of range

Can anyone help me? I've got a workbook that has about 8MB of data, but with all the formulas and pivots it balloons to 25MB (I already made sure the pivots are using same datasource, etc - it seems to be the additional formulas that are the issue - hence I want to get rid of them when I share the workbook with others who don't update the book anyway).

Please Login or Register  to view this content.

[scott.s.fower]

the code works for me, you have to have already created the workbook named "Demand Plan Review_APAC.xlsx" and have it SAVED and OPEN for excel to recognize it exists

[arlu1201]

A subscript out if range error occurs when the sheet name in the code and the sheet name in the file dont match. Check the names and run it again.

[Gerlof]

OK, that works. However, is it possible for the macro to open a new workbook and paste the data as value (and save it to location 'C:\Foldername\filename' also?)

[arlu1201]

Try this

Please Login or Register  to view this content.

[Gerlof]

Great, that works now, file size dropped from 25MB to just under 10MB

One last question. I tried to save the file as "xlsb" (since it looks like that will drop it down to 5.5MB). But if I change the code to filename.xlsb it doesn't quite like it.

[arlu1201]

You will need to put in the file format for all files post excel 2007. So replace this line

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

[Gerlof]

thanks thats perfect now!