Ok here goes - first post

I am currently trying to pull together various strands of VBA code with the intention of producing a user form to enable a number of different users to consolidate data from a varying number of workbooks into a single report. I can do this with the consolidate button but it is a lengthy task and requires some explanation. therefore I am trying to automate the process. At present (I am new to VBA programming) I am able to consolidate the sheets by adding the location to the code, however that is obviously not practical as the donor documents are variable in quantity. The data is in the same named worksheet and location inn terms of column and row in all the workbooks.

I have got about 80% of the thing working but can not sort out how to access the open workbooks (ie; I can select and open the workbooks and I have coded the "consolidate" process but need to join the two together. What I am trying to achieve, in principle, is not dissimilar to RDBMerge add in but I want to Consolidate not merge plus I would not be able to use and add-in due to resistance from the IT dept,and I can't access RDB's code :-)

Would appreciate advice on how best to pick up the relevant areas from the workbooks so I can continue on this steep learning curve, without tipping over the top.