Link to home
Create AccountLog in
Avatar of Freek Doornkamp
Freek Doornkamp

asked on

Linking multiple excel workbooks and gathering data in one single sheet


I am new to working with VBA and I'm probably trying to do  something above my level of knowledge.
I want to link multiple Excel Workbooks from one folder and gather specific cells (data) in one single sheet.

I got the following code working, but it only gives the output of all files in one workbook, with the value of cells A4: C4.


Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range

' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)

' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\603964\Desktop\Test"

' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1

' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")

' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)

' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName

' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")

' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _

' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value

' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count

' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False

' Use Dir to get the next file name.
FileName = Dir()

' Call AutoFit on the destination sheet so that all
' data is readable.
End Sub

now I want to select multiple cells from the files that are in the folder, but here I get stuck.
unfortunately I can not manage to select multiple cells and get them in a format.

The output needs te be something like this:

      A                 B      C     D      E      F      G     H     I       J
1 Filename 1      A9      B9      C9      A10      B10      C10      A42      B42      C42
2 Filename 2      A9      B9      C9      A10      B10      C10      A42      B42      C42
3 Filename 3      A9      B9      C9      A10      B10      C10      A42      B42      C42
4 Filename 4      A9      B9      C9      A10      B10      C10      A42      B42      C42
5 Filename 5      A9      B9      C9      A10      B10      C10      A42      B42      C42
6 Filename 6      A9      B9      C9      A10      B10      C10      A42      B42      C42
7 Filename 7      A9      B9      C9      A10      B10      C10      A42      B42      C42
8 Filename 8      A9      B9      C9      A10      B10      C10      A42      B42      C42

Could someone please help me with my challenge?
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Eww, what an ugly piece of code .....

Short answer:
You just need to modify the range in the following line (replace A4:C4 by whatever suit your needs):
Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")

Open in new window

And, can you be more precise about what you call: " multiple cells" ?
Avatar of Freek Doornkamp
Freek Doornkamp


Hi Fabrice,

Thx for your reply! I am not proud of the code, but I have to make a start.

This time I made an example in an Excel file (see attached file).

I have a folder containing 200+ Excel files that have to be read in on a quarterly basis.
Now only a few cells are interesting to me and I would like to have them automatically selected and have the output placed in a new worksheet.

Because  the files are submitted by several people, I want the name of the file in the output. Followed by the desired Cells from that specific file.

Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4")

Open in new window

Where i get stuck is to select multiple data and place it in the correct order (see attached file).
I have tried to expand the range with ";" but this does not work.

Set SourceRange = WorkBk.Worksheets(1).Range("A4:C4; A8:C8...etc")

Open in new window


Do you have a better idea for me?
Avatar of Ejgil Hedegaard
Ejgil Hedegaard
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Dear Ejgil Hedegaard,

Many thanks for your update and help!
The code works really good.

I will go through the code to learn how it works.
If I have any questions, can I ask those to you?

Best Regards,

Yes you can.