Pulling/Collating Data from Workbooks

[chrisk]

Hi,
I have a workbook with 31 sheets a diary called April for this month
AX to FX is the info I want relating to the employee's initials in the G
column of the diary
There are multiple entries for the employee so he may reappear in row 9, 13
and 15 in column G of the diary

I want to pull all the lines that relate to that employee from the 31 sheets
of the Diary workbook called April for this month into a separate workbook
called BOB (employees name) and BOB, his name, is the reference in the diary
in column G (also in other cells if Bob does more than one job per day) but
in the G column.
There is only one sheet in BOB called Sheet1

There is a workbook for each month with 30/31 days (Feb with 28/29)

So I want to search the 30 worksheets (named 1 to 30) of the Diary (April)
for Bob in the G column
Then pull the data from columns A to F that relate to the row that Bob
appears in and put it in the separate workbook called BOB
There are the other 18 employees that I want to do the same with who also
populate the G column of the same Diary and who also have their own separate
workbook.

Thanks

 

[Roger Govier]

Hi Chris

Whilst it is possible to do what you want, by writing VBA code, I would
urge you to reconsider your recording structure.
You would be much better served by having all data for all people for
all days of the year in one single spreadsheet.
It would then be a simple process with either Autofilter or Advanced
Filter to produce a report for any employee for any tine period, without
having to have 30 separate workbooks.

I am assuming that you have fewer than 300 entries per month for each
employee if you are using XL2003 as 300 x 12 months x 18 employees would
be 64,800 rows of data.

If you had XL2007, then it would not matter if you had 4,500 or so
entries per employee per month

Let us assume that all of your data is in columns A to G at present.
In column H enter the heading DATE in H1 and in H2 onward the date that
BOB or JIM did the work in your regional format (here in the UK it would
21/04/2010). From a data entry viewpoint, typing Control + ; will
automatically insert today's date into a cell.
In column I enter MONTH in I1 and in I2 enter
=IF(H2="","",TEXT(H2,"mmm"))
and copy down.

Now, highlight A1:H1>Data>Filter>Autofilter and this will apply dropdown
arrows on each of those cells.
When you want a report, click the dropdown on column G and Select BOB,
and then click the dropdown on column I and select Apr ( or any other
month you want).

If you wanted the report on a different sheet, then you could use
Advanced Filter plus a tiny piece of VBA code, to allow you to enter a
Name and a Month, and all of the relevant data would be pulled across to
your report sheet.

Post back if you need more help

 

[Duke Carey]

I hate to be the bearer of bad tidings, but by structuring your workbooks in
this fashion you've created a reporting nightmare. A far better approach is
to have all the data in a single sheet WITH A DATE COLUMN. Then it is
trivial to extract the data or create sopisticated reports.

Ron de Bruin has some tools that might allow you to pull all this data
together into a single sheet. See

http://www.rondebruin.nl/merge.htm

To employ that tool you will probably still have to visit each sheet and
add a date column to the data so that Ron's macro will generate a list that
contains dates.

If you cannot pull all the data together then you are left with brute force:
visit each page,
sort or filter by by employee,
copy,
and paste
all of which will have to be repeated for the next reporting effort.

Sorry

 

[chrisk]

Thanks to both of you,
I'm not particularly worried about the date, I just want all of 'Bobs Jobs'
pulled from the April workbook to the Bob Workbook
I've no idea about VBA, wouldn't even know where to start

 

[chrisk]

Thanks
Read through the Rondebruin stuff, I know how to cut & past, so that bit is
OK, but how do you run the VBA, if that is what you do with it

 

[Duke Carey]

Chris - take a look at Ron de Bruin's add in. If you put copies of all your
workbooks in a single directory, then ran his add in, I think you could pull
all the info together into a single file, on a single worksheet. From there
it is a simple matter to filter or sort to segregate the data you want and
then copy it to another, separate workbook.

By the way, if you don't care about the date, why separate all the data into
different worksheets/books BY date? Seems like lots of trouble for no
(apparent) benefit

 

[Roger Govier]

Hi Chris

Alt+F8 to bring up Macros
Highlight the macro name
Run