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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Broadcast[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Kolhser Bear
[/TD]
[TD]12/25/14[/TD]
[TD]12:00[/TD]
[TD]Stuff[/TD]
[TD]$0.10[/TD]
[TD]North Pole[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Active[/TD]
[TD]Doll[/TD]
[TD]Target Range[/TD]
[TD]11/13/14[/TD]
[TD]1:45[/TD]
[TD]That Stuff[/TD]
[TD]$0.13[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Sears Our Us[/TD]
[TD]7/4/14[/TD]
[TD]2:30[/TD]
[TD]This Stuff[/TD]
[TD]$500.34[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Wally World[/TD]
[TD]11/25/14[/TD]
[TD]4:20[/TD]
[TD]Other Stuff[/TD]
[TD]$4.20[/TD]
[TD]Georgia[/TD]
[/TR]
</tbody>[/TABLE]
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”
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Doll[/TD]
[TD]Target Range[/TD]
[TD]11/13/14[/TD]
[TD]1:45[/TD]
[TD]That Stuff[/TD]
[TD]$0.13[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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.
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Broadcast[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Kolhser Bear
[/TD]
[TD]12/25/14[/TD]
[TD]12:00[/TD]
[TD]Stuff[/TD]
[TD]$0.10[/TD]
[TD]North Pole[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Active[/TD]
[TD]Doll[/TD]
[TD]Target Range[/TD]
[TD]11/13/14[/TD]
[TD]1:45[/TD]
[TD]That Stuff[/TD]
[TD]$0.13[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Sears Our Us[/TD]
[TD]7/4/14[/TD]
[TD]2:30[/TD]
[TD]This Stuff[/TD]
[TD]$500.34[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Doll[/TD]
[TD]Wally World[/TD]
[TD]11/25/14[/TD]
[TD]4:20[/TD]
[TD]Other Stuff[/TD]
[TD]$4.20[/TD]
[TD]Georgia[/TD]
[/TR]
</tbody>[/TABLE]
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”
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Item[/TD]
[TD]Store[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Description[/TD]
[TD]Price[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Doll[/TD]
[TD]Target Range[/TD]
[TD]11/13/14[/TD]
[TD]1:45[/TD]
[TD]That Stuff[/TD]
[TD]$0.13[/TD]
[TD]Michigan[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
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.
