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.
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.
' Use Dir to get the next file name.
FileName = Dir()
' Call AutoFit on the destination sheet so that all
' data is readable.
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?