Macros

Hi Colin

Thank you for your question; and welcome to the forum.

You could try creating a macro to automate this process by doing the following:

1. Go to Tools - Macros - Record new macro.
2. Give your macro a name. The name cannot include spaces. You can also allocate a shortcut key combination that will run the macro if you wish.

3. Once you click OK, Excel will start recording the actions you take in Excel. There should be a small toolbar with a blue square button on it which you can press to stop recording the macro. If you can't see it, it doesn't matter as you can stop the macro recording another way when you have finished anyway.

4. Go through the steps you would normally take to pick up and process the file concerned.

5. When finished either click the Stop Recording button on the small toolbar; or go to Tools - Macro - Stop recording.

6. To run (test) the macro go to Tools - Macro - Macros, select the macro and click Run; or use the shortcut keys to run the macro if you created these.

7. If the macro doesn't work or you get errors, then you can delete the macro by going into Tools - Macro - Macros, select and press Delete.

If you are doing something more complex that cannot be run via a macro as described above, then you may need to write VBA code to get the macro to work.

We have an Excel Advanced course which includes an introduction to macros; and a 2 day Excel VBA course which looks at coding more complex macros.

I hope this helps.
Amanda