Vba to look for non 0 values and copy them to another workbook

[spill-the-beans]

Hello I've learned a little bit from you all to at least try and start my own code, but I'm completely lost this time.

I have a folder with about 120 text files in there. In a header-named column of each text file there are numerical values. I'm trying to create some code that will open every file, look for the non 0 values, and copy the values to another column in another workbook.

• First, the name of the open text file needs to be copied across in the next empty row of the results file.
• Then, values should be looked for in the named column of the text file.
• If a non 0 value is found, it should be copied and pasted into the results book, in the same active row as the pasted name of the file it came from, but the next empty column.
• If I had 120 text files, each with up to 2 non 0 values in the named column, the results file would end up with 120 rows, with 3 columns (text file name, first found value, second found value).

There can be any number of 0s and up to about 5 non 0s in the column, but to demonstrate:

In file 1, named "Tom_1.Word1"

[TABLE="width: 500"]
<tbody>[TR]
[TD]Result[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]2[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]


In file 60, named "Tom_60.Word60"

[TABLE="width: 500"]
<tbody>[TR]
[TD]Result[/TD]
[/TR]
[TR]
[TD]15[/TD]
[/TR]
[TR]
[TD]17[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]19[/TD]
[/TR]
</tbody>[/TABLE]

In file 120, named "Tom_120.Word120"

[TABLE="width: 500"]
<tbody>[TR]
[TD]Result[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
[TR]
[TD]100[/TD]
[/TR]
[TR]
[TD]110[/TD]
[/TR]
[TR]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]


In another file, the information I need to get out, should be:

[TABLE="width: 500"]
<tbody>[TR]
[TD]File[/TD]
[TD]Result 1[/TD]
[TD]Result 2[/TD]
[TD]Result 3[/TD]
[/TR]
[TR]
[TD]Tom_1.Word1[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Tom_60.Word60[/TD]
[TD]15[/TD]
[TD]17[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]Tom_120.Word120[/TD]
[TD]100[/TD]
[TD]110[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Can anyone help with this please?