Hi,
I have 140 .xlsx files from which I have to copy cells M3:M8 from sheet "Test" into Once Excel in sheet "Semle". I am using the code that was posted here: Copy data from Multiple workbooks simultaneously into One excluding first row - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum. It works perfectly except I would like to insert a transpose function as well, since the data I am copying is in rows (M3:M8) and I would like to have it in columns (A:G). Can someone please help me inserting the transpose function, as I did not manage to do it myself (it's my first vba attempt)?
Thank you!
Code
- Sub Maybe_Like_So()
- Dim wb As String
- Application.ScreenUpdating = False
- wb = Dir(ThisWorkbook.Path & "\*")
- Do Until wb = ""
- If wb <> ThisWorkbook.Name Then
- Workbooks.Open ThisWorkbook.Path & "\" & wb
- With Workbooks(wb).Sheets("Test")
- .Range("M3:M8").Copy ThisWorkbook.Sheets("Semle").Cells(Rows.Count, 1).End(xlUp).Offset(1)
- End With
- Application.CutCopyMode = False
- Workbooks(wb).Close False
- End If
- wb = Dir
- Loop
- Application.ScreenUpdating = True
- End Sub