I receive auto-generated daily reports on various sales metrics on an associate/daily level. I need to be able to specify an associate via their unique parameters (store number + last name + first name, for instance) and have a macro go through my reports from each day pulling their one line and dropping them into another sheet for analysis. I don't know if I should get each report into one workbook (copy all the sheets into one workbook?) or if (since the sheet name is the same across all workbooks) a macro could just go through every XLS in a folder.
The only other thing I need it to do is differentiate where each line came from - in a static location on every workbook is the generation date, so if when it pulls an associate from 1/17's report and drops it into the summary sheet it puts the date next to them it'd be perfect.
I'm versed in Java, however databasing and VBA is very new to me so pardon me if I've made any crazy assumptions.
Attached is a sample with names deleted; the one thing that might make it tricky is when I get the reports its usually a two-sheet thing; theres a rolling MTD page I don't use or care about in each real XLS. However, the sheet I want data from is always named Sheet1, with only the workbook name changing.
Last edited by CapnSef; 02-07-2012 at 07:20 PM.
bump no response
Should the data for the associate be copied to one worksheet only in a new workbook?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Yes, the data can be in a totally separate document. The one issue is the data is being pulled across several other workbooks from the same directory on the PC. However, they all have the same Sheet1 in them. Thank you again so much!
Edit: So, to clarify, if I have 10 XLS documents in a directory it should open each one, search for an associate, and if it finds that associate it should copy that row along with the date into another (new) workbook. It should repeat this process, compiling all of that associates appearances into one sheet alongside the date the row was pulled from. On the example I provided, the date occurs in the static cell of C2.
Last edited by CapnSef; 01-21-2012 at 10:35 PM.
Bump no response (let me know if you have any other questions Arlu!)
Bump no response (I hope this isn't an outlandish request :/)
Sorry, as i was working on your request i faced another problem - How will the associate be selected? Will it be a dropdown? Or should this task be done for all associates one at a time?
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
A fill-in GUI would be exceptional; I figured I could even just go into the script and change the variables myself. For instance, one variable for Store #, one for Lastname, one for Firstname?
But would you want to do this each time you run the macro? Instead you can have a dropdown on a main page (sheet1 or something) so you can then change it in the front and the code takes in these changes.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
That sounds to be very convenient, arlu1201. So long as the dropdown could handle such a high number of records in its list, that would be a very good solution. You're right, each time I run the macro it would be on a different associate.
Edit: arlu, does your script assume I have multiple workbooks or one workbook with many worksheets? I can do either, I just wasn't sure which route you've taken.
Last edited by CapnSef; 01-25-2012 at 08:23 PM.
Bump no resposne
It depends on your layout. You select whether you want multiple workbooks or 1 workbook with many worksheets. Ideally, to make the code less complicated, the 2nd option (one workbook with many worksheets) is better. You tell me what you choose and i can create your code with that.
Cheers,
Arlette
If I helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
Completely understandable, arlu.
Luckily, thanks to user snb on these forums I have this code:
Which compiles all my annoying workbooks into one master workbook, with each day as a separate sheet.Sub snb() c01 = Dir(ThisWorkbook.Path & "\*.xls") Do Until c01 = "" if c01<> thisworkbook.name then With GetObject(ThisWorkbook.Path & "\" & c01) .Sheets("Sheet1").Copy , ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count) .Close False End With end if c01 = Dir Loop End Sub
So, at our present state, the macro simply needs to be able to run within a workbook with several sheets named "Sheet1(1)," "Sheet1(2)," etc and pull a particularly defined associate out and plop them into a new workbook/sheet/whatever alongside the date from the sheet they were pulled (I think cell C2 always contains the date).
So the output would be like:
1/17 (associate row)
1/18 (associate row)
1/20 (associate row)
Some days an associate may not work or have any data, so it would be understandable if days were skipped - but every time an associate appears on a sheet, he or she should be copied out if he or she meets the defined critera.
Please let me know if things are unclear and I GREATLY appreciate all your patience!
Bump no response
Arlu
Please let me know if you encounter any other snags or questions in our efforts to find a soluton to this
Thank you again so much
Bump no response
Arlu, is there anything I can do to help you with this or do you think this is something you're unable to do?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks