VBA to consolidate data from different workbooks into one

[gertie193]

Hello,

For Excel 2010:

I've had to create a new workbook where I had to copy and paste the data from 27 other workbooks into this new workbook. There has to be a better way!

The data from the 27 other workbooks all come from 4th tab (sheet) that includes the phrase 'Leading Indicators' (it may have other words). The data always starts on the 5th row (but has a title on the 1st row, followed by a blank row on the 2nd row, with headers on the 3rd row, with more headers on the 4th--I don't need this info), is always 6 columns wide, but the number of rows vary.

How do I get the data from these 27 workbooks into one new workbook automatically where the data will be copied and pasted directly underneath each other? Sometimes the last row of data from the 27 workbooks ends with a empty row colored black--I don't know if that is a problem.

**I think it would be wiser to have a solution in case data should be arranged differently at times, e.g., maybe it could start on another row besides the 5th, maybe the 3rd or the 6th, etc., or the data vary with the number of columns, or the 4th tab contains another phrase. I just want to see if there is a flexible solution.**

I would really appreciate any help b/c there has to be a better way to do this. It was too labor intensive.

 

[dermie_72]

VBA would be much easier.

If the files that you're capturing the information in are all in the same directory you could use the for each file in folder loop

excel - Loop through files in a folder using VBA? - Stack Overflow

Then you could use the for each worksheet in worksheets loop

http://www.mrexcel.com/forum/excel-...help-looping-through-all-sheets-workbook.html
Macro to Loop Through All Worksheets in a Workbook

Then to copy the data for variable ranges, you could use the last row variable for copying from the source sheet to the target sheet lastrow +1 (otherwise you overwrite the last row)

Excel Find the last row of data in a range

I hope that helps.

 

[gertie193]

Thank you for your reply. I will review this over the weekend. Could I ask some questions, if I need some help? I not super savvy with VBA.

VBA would be much easier.

If the files that you're capturing the information in are all in the same directory you could use the for each file in folder loop

excel - Loop through files in a folder using VBA? - Stack Overflow

Then you could use the for each worksheet in worksheets loop

http://www.mrexcel.com/forum/excel-...help-looping-through-all-sheets-workbook.html
Macro to Loop Through All Worksheets in a Workbook

Then to copy the data for variable ranges, you could use the last row variable for copying from the source sheet to the target sheet lastrow +1 (otherwise you overwrite the last row)

Excel Find the last row of data in a range

I hope that helps.

 

[dermie_72]

Post any questions you have here, to ensure that is anyone else experiences the same issue, there will be a solution for them too.

The links should be explain all of the functions you want. If you're not used to writing code, then there are many examples to work with, either by google, or even just here.

One of the best things you can do if you're not experienced in vba is to record the action you want to do, then go into the VBA editor (Shift + F11), and look at the code.
Make sure that both the VBA editor, and excel are visible, then then do a step through process. (Press F8). This will show you what the code does to excel. You can then use whatever code you need to do what functions you want.

Again, the links should provide you with the foundation required to do what you're after. Your first attempt may not be the most efficient, but that comes with practice.