Macro to locate and copy data from other workbooks, on a specific worksheet and row based on a value

[thunt]

Hello and good Friday,

I am using Windows 7 and Excel 2010.

I am in need of a macro that can pull a row of data (based on a value in a column), from a specific worksheet (in every workbook in a specified location), and insert that row of data in to a workbook on a new row, and then do the same thing on the row below. Also if I rerun the macro it clears all the current data and gets the latest. so i will try to break it out in detail below...

---------------------------------------------------------

1.
I am looking for a way to have a Workbook that is named “Summary” (located in "C:\partsandlabor"), pull data from all workbooks in a specified location ("C:\partsandlabor\allparts”).
The "Summary" workbook would be doing the macro on the worksheet named "All Items". Below is a example of what it would look like before running the macro for the first time:

"Summary" Workbook, "All Items" Worksheet Sample

	A	B	C	D	E	F	G
1
2	Item	Store	Date	Time	Description	Price	Location
3
4
5
6

<tbody>
</tbody>

2.
The data that needs to be pulled in each of the workbooks in "C:\partsandlabor\allparts" is on a worksheet named “Change Log” (other worksheets exist but only “Change Log” is needed). Within "Change Log", there are rows/columns of data. I need the macro to find the row in column "A" that has "Active" in it, and copy that data from that row from column B:H and bring it over to the first empty row in workbook "Summary" on worksheet "All Items". (So for the example below it would be be copying row 4 that, as that is the row that "Active" is currently typed in)

(First workbook opened in specified location), "Change Log" Worksheet Sample

	A	B	C	D	E	F	G	H
1
2	Broadcast	Item	Store	Date	Time	Description	Price	Location
3		Doll	Kolhser Bear	12/25/14	12:00	Stuff	$0.10	North Pole
4	Active	Doll	Target Range	11/13/14	1:45	That Stuff	$0.13	Michigan
5		Doll	Sears Our Us	7/4/14	2:30	This Stuff	$500.34	California
6		Doll	Wally World	11/25/14	4:20	Other Stuff	$4.20	Georgia

<tbody>
</tbody>

3.
After the macro ran on the first workbook it found in "C:\partsandlabor\allparts”, it would paste/insert it in the "Summary" workbook, "All Items" worksheet like below.

"Summary" Workbook, "All Items" Worksheet Sample: After running once on only one workbook in "C:\partsandlabor\allparts”

	A	B	C	D	E	F	G
1
2	Item	Store	Date	Time	Description	Price	Location
3	Doll	Target Range	11/13/14	1:45	That Stuff	$0.13	Michigan
4
5
6

<tbody>
</tbody>

4.
If there were other excel files in "C:\partsandlabor\allparts”, it would do the same as Step 2, but would paste/insert the data on the next available row in the "Summary" workbook, on "All Items" Worksheet (row 4 in this scenario), until it had ran through every workbook in "C:\partsandlabor\allparts”.

5.
I also need the macro so if I run it a second time/again (to update the "All Items" worksheet) it would delete all the current data from A3:G3 and down, and rerun Step2-Step4 to update (in case Active was moved do a different row in one of the workbooks in "C:\partsandlabor\allparts”.


I hope that makes sense, I tried to step through what I am trying to accomplish. Please let me know if you need me to clarify anything more or confused by it.

Thanks in advance.

 

[thunt]

-bump-

 

[thunt]

-bump-

 

[thunt]

-Bump-

please can someone help get me pointed in right direction.