I have 300 excel files each with the exact same format - 4 worksheets in each file with same column/row headers. I am trying to extract information from select cells of each of these 4 worksheets (for example, from cell D26 in worksheet A, from cell D62 in worksheet B, etc) from all 300 excel files and put that data into a new master excel file that contains the information from each of the 300 excel files. Is this possible?
1 Answer
There is some really nice code posted here that should do what you want with a few tweaks.
Gordon
Some changes will need to be made which is nicely commented. These rows get all the data from column A in the workbook and copy it to the master sheet.
LR = Range("A" & Rows.Count).End(xlUp).Row 'Find last row
Range("A1:A" & LR).EntireRow.Copy .Range("A" & NR)
Instead you will need to loop through the worksheets and set the ranges you want copy with something like this.
wbData.sheets(1).range("A2:A26").copy .Range("A" & NR) NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 wbData.sheets(2).range("A2:A62").copy .Range("A" & NR) NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 ...