How do I save an excel workbook keeping only formatting and the values

[burnst83]

I would like to know if there is an efficient way to save an excel workbook, with multiple worksheets as an .xls. I want the file to keep the formatting from the original file, while only saving the values of the original file. All the numbers in the original file are hyperion driven but the file is going to be emailed to people without hyperion. Is this possible??????

Thanks in advance for any help, I have a ton of workbooks that are saved and emailed every month so copying and pasting values, then pasting formatting for every sheet gets mighty tedious!

[teylyn]

Hi,

how about recording a macro for the copy - paste values, copy - paste formats operation and run the macro when you need to create a workbook copy?

[burnst83]

Good idea! I'll do that! Excuse my ignorance but is there a way to record the macro in a way so that every worksheet in the workbook will be copied and pasted, so i can run it on workbooks with varying numbers of worksheets?

[teylyn]

You can record the macro for one sheet, then you need to manually add a loop to include all worksheets. If you need help with this, post your recorded code in the programming forum and ask for that code to be included in a loop for all sheets. Someone there will pick it up.

Remember to use code tags like this [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] when posting code!

hth

[burnst83]

Thanks for all of your help! I'll start researching how to record loops in the Programing section of the forum!

[teylyn]

That's just the thing. Loops can't be recorded, they must be written. I'm just starting with VBA, so a lot of the finer points will escape me. But the experts in the programming forum will see you right!

cheers

[burnst83]

hahaha, i was wondering why i couldnt find "how to record a loop" anywhere on this site or elsewhere! Thanks again, i'll work on it and look in the programming section