Search within range for first populated cell and copy to new workbook

Hi all,

I have been working on my current project for a week or so now and am learning vba as I go along. I have received lots of help and advice from other forums but have now hit a major hurdle.

I am running a looped search and retrieve data module over hundreds of files running over a five year period which basically extracts data from the files that have predefined criteria ie cell specific, and pastes the data into a new workbook to create a more user friendly order history in one place rather than go through each file individually every time.
The files that the data is extracted from are common, ie a template, and the same cells are populated throughout.
However, on closer inspection, in one area, sometimes the data is not in the correct position. Secondly, subsequent amendments are placed in consecutive sheets. ie original in sheet 1, issue a in sheet 2 issue b in sheet 3 etc.
From the above, I have two questions.
Q1 I need to prepare a vba statement within my code that literally says, find the first populated cell (with any content) within a specified range, copy the data and paste it in the position specified.
Q2 When extracting data from subsequent sheets where the files have been amended, is it possible to highlight this in my history sheet by adding (2) when the data has been extracted.

The following code is a segment and I have attached one of the template files which is consistent with the rest of the files searched.

Any help would be greatly appreciated.

Cheers

Please Login or Register  to view this content.

The range I wish the search to be in is A14:A38 and I have put the segment where it is to ho in bold.

Once again,

Thanks

Jeff

Can this be done?

If so, I would appreciate some help in undertanding the way it works