Automate replacing formulas with values?

[pinny]

I have a spreadsheet that opens up an exported data file from my accounting app. I have a second spreadsheet which sets up a P&L using the exported data spreadsheet.

I would like to save one of my customs views on the P&L spreadsheet with its exact layout but using the cell values only. I need to set this up so my secretary can do this without me on an as needed basis.

I know we can copy cells and use paste special to paste values. Is there a way to automate this for an entire spreadsheet without loosing the format and give my secretary an easy way to produce the desired new spreasheet with values only?

Thanks
Pinny

[WinteE]

Hi Pinny,

You best use the macrorecorder for this. Tools, Macro, Record new macro.

Once you started the macrorecorder you select the entire worksheet by clicking the most left part on top of your worksheet (left from A, above 1) and then Copy, Past special, Values.

After this you end the macrorecorder session.

The macro that you created will probably be named Macro1. You can link this macro to a Ctrl+Shift+(a character) with Tools, Macro, Macro's, select Macro1, Options, Shift+(a character).

Now you can start this Macro with Ctrl+Shift+(a character).

Succes,

Erik