VBA to transfer data from multiple workbooks to one central spreadsheet

[k_duffy]

Hi,
I am using the below code to transfer data from 10 individual spreadsheets saved in one folder called the ‘suppliers-master’ which is in a column format to a master spreadsheet ‘zmaster’ saved in the same folder. I need the data to paste in a row format one after the other so that I have 10 rows of data at the end. Additionally, some of the fields in my individual spreadsheets I would like to ignore either because I don’t need the information on the master or because they are blank.
The below code copies and paste the data fine into the master spreadsheet but it is in a column format and the 10 separate spreadsheet’s data follow one after the other in a long column down A1.
At the moment I am just manually transposing the data to rows.
Hope that makes sense, and if anyone has a solution it would be greatly appreciated!
Thanks
Kirsty
Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\suppliers-master\")
Do While Len(MyFile) > 0
If MyFile = "zmaster.xlsm" Then
Exit Sub
End If
Workbooks.Open (MyFile)
Range("B3:b45").Copy
ActiveWorkbook.Close

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 15))

MyFile = Dir

Loop

End Sub

[JLGWhiz]

Hi k_duffy, welcome to the forum.
It does not really make sense, but see if this is what you want.

Please Login or Register  to view this content.