VBA Marco to extract data from multiple workbooks and compile data in a single workbook

thunt

New Member
Joined
Aug 14, 2013
Messages
24
Hello all,

I am new and still learning macros, using Excel 2010 on Wins 7. I am in need of finding/creating a macro that's purpose would be to:


  1. Go through every workbook in a defined directory (C:\test)
  2. Locate the Worksheet named "Change Log", in every single Workbook in C:\test
  3. In the "Change Log" Worksheet, find/locate the row in which column A has a "X" in it
  4. Copy that row from B:Y
  5. Paste the copied data into the Macro Workbook (this macro Workbook will not be saved in C:\test) starting in B4:Y4 and continuing down until the macro has ran through every Workbook in the defined directory (C:\test)
  6. Upon running the macro again, it clears the current data that was previously copied
I have been going through the search function on this forum and others trying to find a macro that does this, but have not had any success in finding a macro that only extracts the data from a specific Worksheet, based on a value in a cell (almost like a Vlookup).

Thanks in advance!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

thunt

New Member
Joined
Aug 14, 2013
Messages
24
Hello all,

I am new and still learning macros, using Excel 2010 on Wins 7. I am in need of finding/creating a macro that's purpose would be to:


  1. Go through every workbook in a defined directory (C:\test)
  2. Locate the Worksheet named "Change Log", in every single Workbook in C:\test
  3. In the "Change Log" Worksheet, find/locate the row in which column A has a "X" in it
  4. Copy that row from B:Y
  5. Paste the copied data into the Macro Workbook (this macro Workbook will not be saved in C:\test) starting in B4:Y4 and continuing down until the macro has ran through every Workbook in the defined directory (C:\test)
  6. Upon running the macro again, it clears the current data that was previously copied
I have been going through the search function on this forum and others trying to find a macro that does this, but have not had any success in finding a macro that only extracts the data from a specific Worksheet, based on a value in a cell (almost like a Vlookup).

Thanks in advance!

A little more explanation about it if it helps...

Ihave a directory (C:\test) that is full of a .XLSM files. Each one ofthese files has a Worksheet in it named "Change Log" with datastarting in A29:Y29. In Column A I have a cell marked with a "X" thatis the current/active data to use in columns B:Y of that row. I am trying tocreate a new master .XLSM file that can update and give the latest data (B:Y)from every Change Log Worksheet, based on the row that a "X" is foundin column A.
 

thunt

New Member
Joined
Aug 14, 2013
Messages
24
Still unable to find anything in previous forums, anyone able to help out? -bump-
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top