Forum Discussion
Excel Time Saver
I have about 200 seperate excel files i would like to place a single piece of text in the exact same cell on each file at once, i do not want to have to go through them all seperately and do it.
Charla74 can you please talk me through how I'd use this piece of code (quite new to excel) and just confirm where exactly I'd need to put the text i require, can we use 'JD' as an example of the text i want to put in
Appreciate your time
Ok, first you’ll need to make sure the developer tab is activated in the ribbon; (File > Options > Customize Ribbon > Check the box for Developer).
Now we’ll record a macro to activate the Personal.xlsb; (View tab > Macros drop down menu > Record a Macro). Enter the name of the macro as TEST, select Personal for the workbook (these means the macro is available in Excel rather than a specific workbook), click OK. Now just type TEST in cell A1, enter, now select Stop Recording from the View tab > Macros drop down menu.
Now we’re ready to enter the code; hit CTRL + F11 to open the VBA Editor > from the panel to the left, right click on Personal and from the menu select Insert Module > Paste the code into the panel on the right and click save (or CTRL + S) > Close the editor > hit CTRL + F8 to bring up macros list and you should now see the macro named Edit_Multiple_Workbooks > click it and then Run.
In the code, where you see "Sample Entry to Cell A1" just change this for “JD”......this is currently set to enter in cell A1 [Range(“A1”)].....you will need to update the cell reference here if you need to input elsewhere.
Now we’ll record a macro to activate the Personal.xlsb; (View tab > Macros drop down menu > Record a Macro). Enter the name of the macro as TEST, select Personal for the workbook (these means the macro is available in Excel rather than a specific workbook), click OK. Now just type TEST in cell A1, enter, now select Stop Recording from the View tab > Macros drop down menu.
Now we’re ready to enter the code; hit CTRL + F11 to open the VBA Editor > from the panel to the left, right click on Personal and from the menu select Insert Module > Paste the code into the panel on the right and click save (or CTRL + S) > Close the editor > hit CTRL + F8 to bring up macros list and you should now see the macro named Edit_Multiple_Workbooks > click it and then Run.
In the code, where you see "Sample Entry to Cell A1" just change this for “JD”......this is currently set to enter in cell A1 [Range(“A1”)].....you will need to update the cell reference here if you need to input elsewhere.
- Once you run the macro, the ‘Open’ box allows you to select multiple files by holding CTRL while selecting each one OR select the first one, hold SHIFT click on the last one (be careful there aren’t files in between which you don’t want changed)