User has a MASTER and two UPLOAD files in same Folder.
Code should open each workbook in turn and, if is not the MASTER workbook, copy the data from Sheet 1 of the UPLOAD file, transpose and paste it to next free row on sheet 1 of MASTER, close the UPLOAD workbook, and move on to next one:
Option Explicit
Dim f As Long
Dim Filename As String
Dim wbDst As Workbook, wbSrc As Workbook
Dim wsDst As Worksheet, wsSrc As Worksheet
Sub MergeFilesInFolder()
'Find next free row in Master sheet
With ActiveSheet
f = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
If f < 2 Then f = 2
Set wbDst = ThisWorkbook
Set wsDst = ActiveSheet
'Loop through all files in folder
Filename = Dir(CurDir() & "\*.xlsx")
'Ignore the Master workbook
'**Throwing Runtime Error 438 here
Do While Filename <> "" And Filename <> wbDst
Set wbSrc = Workbooks.Open(Filename)
'Copy all date from Source sheet
With wbSrc
Set wsSrc = .Sheet1
wsSrc.Range("A1").UsedRange.Copy
'Transpose and paste to last row on this worksheet
With wsDst
.Range("A" & f).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Application.CutCopyMode = False
End With
'Close other file without saving changes
wbSrc.Close savechanges:=False
End With
'Move to next workbook
Filename = Dir
Loop
End With
End Sub
Any solutions, suggestions or alternatives welcome as ever.
Ochimus
Bookmarks