Macros

Hi Paul

I need to carry out a little more research before I am able to respond to your question.

Kind regards

Dear Paul

Thank you for attending Excel Advanced course!! I hope you enjoyed the course and benefited from it.

To record a Macro that will paste special Values is quite straight forward.

Please refer to the uploaded Excel file for following instructions.

I created this file from one of the Invoice templates that come with Excel.

The total column had formulae in it.

I inserted a new blank sheet and copied and pasted the whole data from Invoice sheet to Sheet 1.

I entered random values for the Quantity and Unit price in the sheet 1.

I selected the cells containing the results in the total column.

I created choose Tools > Macros > Record New Macro...

I called the Macro Paste_Special and assigned a shortcut key as CTRL+SHIFT+P


Before I carried out the task I pressed the Relative reference button on the Macro toolbar. I then carried out the steps by using CTRL +C For Copy and then choosing Edit Paste Special... from the menu bar.

I chose Value and then pressed OK.

Once I did that I chose the other sheet called invoice and then selected the relevant cells and used my Macro Shortcut key (CTRL+SHIFT+P) and it did exactly what it was programmed to do.

If you want to try the macro please click on the Invoice sheet and select all the cells in the Total column and use the macro shortcut key (CTRL+SHIFT+P) and you should be able to see the values instead of formulae in that column.

I hope this has answered your query.

If this has answered your query then I would request you to please mark the question as resolved!! If not and you have a specific question related to this then please post it as a new question and we should be able to provide you the solution for it!!

Kindest Regards


Rajeev Rawat
MOS Master Instructor 2000 and 2003
MCAS Master Instructor 2007
MCT