Copy data from an open workbook with a variable name

[rosshkerr]

Hi Guys

I am trying to create a sheet that copies staffs efficiency data from a monthly file into a separate sheet to work out their overall efficiency for the year. The problem is that the efficiency file is named for the month and year so I am struggling with code to select to summary sheet on the efficiency file. I have attached examples and there is some code in the PDR efficiency file that sometimes works but the next time I run it, it stops for no reason I can see. The code will have to be run each time you open the next efficiency file to copy the next month so there will be 12 sheets, 1 for each month. Thanks for looking, always appreciated

Cheers

[dflak]

Here is my take on it.

Run things from the control panel.

Create a data directory and put the full path to it in Cell H1. Verify the pattern to look for in Cell H2.

The Get Files button gets the list of files in the data directory. The latest of these files is computed in cell B1.

Then click on the Copy Summary button. This will copy the status sheet to the end of the workbook and rename it.

There are custom modules for Get Files and Copy Summary. The ClearTable module is from my "workbench" - give it a point to a sheet and a table name on that sheet. I "dummied up" a couple of files to test it out. and it will clear the data. I've been using it for about a decade.

[rosshkerr]

Thanks dflak. I will have a look as soon as i get time. Greatly appreciated