Extract data from multiple files in folder and feed a master spreadsheet

[ripperbolt]

Hi all,

I'm completely new to VBA so excuse me beforehand if my questions sound clueless. I just started dipping my toes on this and I'm not even a novice yet.

I've been trying to automate a report I do every week for which I have to go through a list of excel files and extract a set of cells that later will be copied to a master file. So, hoping to make my life easier, I found this script that someone else came up with, it works up to some point to my needs but still falls short on others.

Sub simpleXlsMerger()
Application.ScreenUpdating = False

Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("C:\MyDataPath\")

Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point

Range("B44:C66" & Range("B65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate 'first worksheet of each book, change the number for other sheet
 
'Do not change the following column. It's not the same column as above
Range("B65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next

Application.ScreenUpdating = True

End Sub

Here's what I'm trying to accomplish:

- copy all cells in the range B44:C66 and then paste them on columns C and D of the master file respectively;
- copy cell B40 and paste it on column B of the master file, filling all the 23 rows (B2 to B24) with the same value;
- read off the month from the date on cell B14 and paste just the month value on those 23 rows on column A (A2 to A24)
- I'm skipping the first row since the spreadhseet will have headers

So far, I've been able to go through the first item on my list below, sort of...from the first file it copies 25 rows instead of 23, from the second file it copies 29, and on the third file it goes through the same loop again: 25 rows on the 3rd, 29 rows on the 4th and so on. I can't seem to find a reason for this. Any clues? I've read many of the threads here on the same topic of looping through all the files on a folder but since all are very specific it's been hard to find a similar case.

Any help you can provide will be greatly appreciated.

Thanks!!!

 

[JLGWhiz]

Range("B44:C66" & Range("B65536").End(xlUp).Row).Copy

This line of code tells vba to copy starting at cell B44 and whatever else is below that in columns B and C. It is a poorly written statement since it goes down to over 6600 rows. So apparently your different sheets in different workbooks have variable length columns of data which results in variable length ranges of data being copied to your consolidated sheet. If you want the copy range fixed at B44:C66 Then replace the above statement with:

Range("B44:C66").Copy

But at the very least remove the 66 so the statement only goes to the last row or data, i.e.

Range("B44:C" & Range("B65536").End(xlUp).Row).Copy

This would still give you variable length columns depending on what the column content is.

 

[ripperbolt]

Hi JLGWhiz, thanks so much for your reply. Your first suggestion worked like a charm for that range I was after. Trying to figure out a solution for the B40 cell I mentioned earlier, that's where the country name is defined matching the data stored on the B44:C66 range... let's see if I can make it work and I'll post my findings later. Thanks again!

 

[JLGWhiz]

Hi JLGWhiz, thanks so much for your reply. Your first suggestion worked like a charm for that range I was after. Trying to figure out a solution for the B40 cell I mentioned earlier, that's where the country name is defined matching the data stored on the B44:C66 range... let's see if I can make it work and I'll post my findings later. Thanks again!

For the B40 issue.

ThisWorkbook.Sheets(1).Range("B2:B24") = bookList.Sheets(1).Range("B40").Value

 

[ripperbolt]

Thanks a bunch JLGWhiz, that definitely did the trick!!

 

[JLGWhiz]

Thanks a bunch JLGWhiz, that definitely did the trick!!

You're welcome,
regards, JLG