If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pulling/Collating Data from Workbooks
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 |
#2
|
|||
|
|||
Pulling/Collating Data from Workbooks
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:H1DataFilterAutofilter 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 -- Regards Roger Govier chrisk wrote: 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 |
#3
|
|||
|
|||
Pulling/Collating Data from Workbooks
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" wrote: 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 |
#4
|
|||
|
|||
Pulling/Collating Data from Workbooks
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 "Roger Govier" wrote: 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:H1DataFilterAutofilter 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 -- Regards Roger Govier chrisk wrote: 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 . |
#5
|
|||
|
|||
Pulling/Collating Data from Workbooks
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" wrote: 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" wrote: 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 |
#6
|
|||
|
|||
Pulling/Collating Data from Workbooks
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 "chrisk" wrote: 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 "Roger Govier" wrote: 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:H1DataFilterAutofilter 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 -- Regards Roger Govier chrisk wrote: 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 . |
#7
|
|||
|
|||
Pulling/Collating Data from Workbooks
Hi Chris
Alt+F8 to bring up Macros Highlight the macro name Run -- Regards Roger Govier chrisk wrote: 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" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|