Merging 6 worksheets onto 1 without overwriting existing data -is it possible??

[SNA400]

Hi All
I hope you can help me with this problem.
This may go on a bit, but please bear with me.
Every morning I receive 6 worksheets from different depots detailing trunk vehicles in and out of their respective location and what it was carrying and I have to manually go through every sheet row by row and enter the data onto a master file. I would love to be able to automate this with one or two clicks to free up at least 1½ hours of my time.
The issue is that not every trunk goes to the same depot every night and that they are not always used!
The worksheets the depot receive are created from one ‘master’ we compile every day, showing which trunks they need to run, and what ones they will be receiving. We will be locking out certain functions within these sheets so they can only edit specific cells and not add rows or columns.
I need a script that will check the master file for blank cells and copy/paste the data from the depot sheet corresponding cell – without overwriting cells that already have data in on the master.
Is this possible? I think it is, but with my minimal VBA skills and understanding it is beyond me.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Please help!<o></o>
Simon

 

[g48dd]

SNA400, are you talking about merging 6 workbooks or 6 worksheets? Have you ever tried using the consolidate button under DATA. I used it a couple months ago to consolidate 12 months to 1 worksheet. Each one of my worksheets was a little different from the other. I was working with water tanks and the same tanks are not filled each night. I need a total of water for each tank for the year. Consolidate was able to find each tank by name and add each nights water for the year, even though some tanks did not receive water on some nights so there was a blank cells. Consolidate just ignored it. My tank names sometimes change also what consolidate did was just add all the water for each tank name, if the name changed then I had to find all the water under that name and add it to where it belonged. I am not sure this is what you are looking for but if it sounds like something you want to play with you can Email me and I will tell you how I set up some test runs before I used it on an actual worksheet.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Ken

 

[SNA400]

Hi Ken

Thanks for the reply.

Yes, combining data from 6 worksheets (on 6 seperate workbooks - 1 worksheet each) onto 1 worksheet

I have tried using CONSOLIDATE but the problem is there are also arrival and departure times on the sheet for each trunk and, as always happens with different locations, they do not agree on the arrival times. not sure if that was clear, for example an entry for a trunk from Rugby leaves at 21:00 with 25 pallets and 12 roll cages on and arrives at Ashton at 00:30 - but the details on the Ashton sheet show 24 pallets 20 roll cages and arrival time of 21:45.

I cannot use AVERAGE, because we use the actual quantities of Pallets & Cages to work out a Fall To Earth figure for local deliveries from each depot, and I cannot use MAX because we have a vehicle fill percentage running off the entered data - and if the data was showing 49 pallets AND 52 roll cages the vehicle fill would be 200%!

I might just have to go and bang some heads together and get them to agree on load figures and arrivals!

I'll keep on looking at CONSOLIDATE, to see if the figures could be used, but really need the 'live data' so to speak to work from.

Thanks again
Simon