Convert Horizontal data to Vertical - loop through files in folder

I have a set of workbooks-all located in the same folder-in which data are shown "horizontally"

i.e. file 1 - sheet1 looks more or less like this:

1 2 3
4 5 6

same with the other files.

what I am trying to achieve is that in another woorkbook, all the data are copied in one vertical vector

i.e

1
2
3
4
5
6 from file 1 and then
1
2
3
4
5
6 from file 2

I can seem to be able to resize the array to the correct size but the only values copied are for the last file. in the example that I have above, the result would look something like:

<empty>
<empty>
<empty>
<empty>
<empty>
<empty>
1
2
3
4
5
6

this is the code I have so far:

Sub Consolidate()

Dim a As Workbook
Dim b As Workbook
Dim x, y(), i&, j&, k&
Dim myPath As String
Dim filename As String
Dim wb As Workbook
Dim Fnum&


Set b = ThisWorkbook

myPath = "C:\Data"

If Right(myPath, 1) <> "\" Then myPath = myPath + "\"

filename = Dir(myPath & "*.xl*")

Fnum = 0
k = 0

Do While filename <> ""

Fnum = Fnum + 1
Application.ScreenUpdating = False

Set wb = Workbooks.Open(myPath & filename)

x = wb.Sheets("Sheet1").Range("B3").CurrentRegion.Value

ReDim y(1 To UBound(x, 1) * UBound(x, 2) * Fnum, 1 To 3)
For i = 2 To UBound(x, 1)
For j = 2 To UBound(x, 2)
k = k + 1
y(k, 1) = x(i, 1)
y(k, 2) = x(i, j)
y(k, 3) = x(1, j)
Next j
Next i

filename = Dir
wb.Close SaveChanges:=False

Loop

With b.Sheets("Consolidate")
.Range("A2").Resize(k, 3) = y()
.Columns.AutoFit
.Activate

End With

Application.ScreenUpdating = True

End Sub

I'd appreciate some help on solving this.

Thanks

If you want to convert all columns in to single column, I would use these lines instead.

Please Login or Register  to view this content.

Thank you for your reply. I should have explained a little bit better.

Data in the source files are on 3 rows. In each of the files, column A is Labels and row 1 is years (a1 is empty) - I attach an example.

The problem that I have is that the final array is only populated with data from the last source file in the folder.

Thanks,

This works. If you want to exclude a blank cell, you need to delete (Comment) that line(If Len(x(i, j)) Then.

Please Login or Register  to view this content.

Hmm!
This line is the culprit.

Please Login or Register  to view this content.

still with the same problem. values copied are only those from last data file. I am realizing now it is something to do with the fact that i am not using a ReDim Preserve on y(), which I cannot actually do because y() is multidimensional. I'll try to find a workaround there - thank you for your help!

No, the redim is correct.
I did not look at your code in details, but 2 issues may need to look at

k = 0

Get rid of this line.
Do you a sheet1 in each workbook opened?

x = wb.Sheets("Sheet1").Range("B3").CurrentRegion.Value
How many sheets do you have in each work book?

Correct, 1 sheet per workbook and that sheet is always called "sheet1"

Okay, give me a couple of minutes for testing.

Okay!
If you want to loop first in all books, load the array in to Y, then when you close the loop, you can load the result in sheet consolidation. This requires persevering the redim.
Here is anther alternative which does not require preserve the redim.

Please Login or Register  to view this content.

Thank you! The solution you provided works perfectly!

The preserve code in module 2 seems to work on my test. Personally, I do not use redimpreserve