Collating 13 workbooks onto 1 master workbook

[Raz139]

Hi all, I am new here and I have spent the last 2 hours searching for an answer so I wouldn't have to bother anyone but unfortunately, I haven't found anything that might work for me. Probably because I wouldn't know how to alter some of the answers I have seen to meet my needs.

I am trying to collate all the information from 13 worksheets which are in 13 seperate workbooks into 1 master sheet. On each sheet I am trying to bring through the populated rows (some are partially populated but these are still needed along with the blank cells).

As each sheet is a work in progress, the amount from each sheet will likely change on a daily basis with some completed items being removed and new items being added so ideally I would like to be able to refresh the master sheet each time I use it.

The ranges being use on each sheet are B6:BH56. The master sheet will also start at B6 and the last column will again be BH but the number could potentially go past 600

If it is helpful, the name of the master sheet (workbook) is "Collated" and the other workbooks and sheets are name "Contracts" "Vacancies" etc.

Many thanks in anticipation of some much needed advice.

 

[VoG]

Take a look here http://msdn.microsoft.com/en-us/library/cc837974.aspx

 

[Raz139]

Thanks for the advice VoG, the problem I have is that I am a real novice when it comes to VB and I have already tried something similar but couldn't get it to work. Probably due to my own lack of understanding in fairness.

One thing I forgot to mention is that I need this to be compatible with excel 2003. I would imabine I could get away with using the merge add in on 2007 but I only have access to 2003 in work where I will be using this.