Anyone fancy a tiny challenge? I’m 95% there with what I want to do but there are a couple of things I can’t figure out. I’ll post the VBA code first:
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)
‘ Directory where files reside.
FolderPath = ThisWorkbook.Path + “\”
‘ 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 & “*.xls*”)
‘ 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.
‘ Modify this range for your workbooks.
‘ It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range(“E62:E62”)
‘ 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, _
SourceRange.Columns.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()
Loop
‘ Call AutoFit on the destination sheet so that all
‘ data is readable.
SummarySheet.Columns.AutoFit
End Sub
I need to be able to extract the data from cell E62 in every Excel file in a folder to provide a total. Each file relates to a 12 hour shift, and the data in E62 is the amount of oil used during that shift, for what it’s worth.
The above VBA will do what I need, but there are a couple of issues:
1) the idea is that there is a “run data merge” type excel file in each directory, so at the end of the month you can open the file, click the button to run the above VBA script and the data appears in that spreadsheet. The above, however, creates a new file and imports all the data to that. How do I make it collate the data in the same spreadsheet that the macro resides? It sounds simple but I can’t figure out how.
2) the SummarySheet.Columns.AutoFit doesn’t seem to do anything.
