Hi
I'm fairly new to VBA and I am struggling to get something to work.
I have a folder with 6 workbooks (single sheet) which I would like to combine and display in one master workbook. Is there an easy way to do this?
Is it possible to get the master to update when changes have been made to the other books?
I have found this code (thanks to SNB) this works to a point - Is it possible to append each sheet to the next available column rather than the next free row? Also is there a way to keep source formatting?
Thanks in advance
Sub consolidate()
Dim sh As Worksheet, lr As Long, fPath As String, wb As Workbook, sh2 As Worksheet, fNm As String
Dim lstRw As Long, rng As Range
Set sh = Sheets(2)
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
fPath = "folder with the mutiple workbooks in it"
If Right(fPath, 1) <> "\" Then
fPath = fPath & "\"
End If
fNm = Dir(fPath & "*.xl*")
Do
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set wb = Workbooks.Open(fPath & fNm)
Set sh2 = wb.Sheets(1)
lstRw = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
Set rng = sh2.Range("A2:S2")
rng.EntireRow.Copy sh.Range("A" & lr + 1)
wb.Close False
fNm = Dir
Loop While fNm <> ""
End Sub
Sub Button1_Click()
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Filters.Add "Excelsior", "*.xl*"
.Show
For j = 1 To .SelectedItems.Count
With GetObject(.SelectedItems(j))
With .Sheets(1).UsedRange.Offset(1).Resize(, 19)
Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(.Rows.Count, .Columns.Count) = .Value
End With
.Close 0
End With
Next
End With
End Sub