So I use Excel at work and last year I found a way to simplify my job using macros. Essentially I’d copy data, paste it in to my “Toolkit” Excel file (A kind of middle man file with formulas and macros), click a format button (A macro that sorted the data, put everything in to the correct format and highlighted all the data) and then paste it in to the Excel file where the data needed to be.
My “Toolkit” file contains formulas and up until today instead of pasting the formulas it would automatically paste the values. Today I came in to work and my computer had been restarted, assumingly due to an automatic update, and it now pastes all the data as formulas from the Toolkit file.
I’m unaware of anything that’s actually changed and looking on Google it seems it probably should have been pasting as formulas the whole time. I know I can just select to paste it as values each time I copy and paste but I’d like to try to understand why I didn’t need to do that before and, if possible, get it back to how it was.
Thanks
If the macro doing the pasting to the last excel file you should be able to change it.
Find the part of the macro that transfers the data to the Excel file
ActiveSheet.Paste
Change it to -
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
You may not need the -Selection.PasteSpecial Paste:=xlPasteFormats- part but it’s nice if you want your numbers to be formatted like they are in the toolkit.
I use a few formatting and processing “toolkits” myself.
OR
If YOU were doing the copy/paste manually then I can think of one possibility as to WHY it was VALUES before you program restarted and that now it’s FORMULAS.
If you left your toolkit open all of the time you may have modified the macro but not saved afterward. The reboot actually takes your file back to the LAST SAVED copy of the toolkit, not the last opened copy of it.
If you want to continue doing a copy/paste and making sure that it’s values only you can just add a page to your tool kit and add a step in your macro that copies and pastes the data as values to the new page. Then do all of your manual copy/paste from your new toolkit page to your final workbook and it will be values.