Need to pull in data to the right of certain sheet's tables using VBA.

Is this possible to do in VBA?
1) Loop through a folder and all subfolders checking each xlsx file's sheet labels. When the sheet label contains the word Paste in any case, do the following
2) Every sheet contains a table. On some sheets, there are a random # of blank columns to the right, followed by 2-4 columns that I need appended to a new sheet.
3) So the vba should loop through all the sheets and append to a new single workbook sheet with those columns. the xlsx workbook and the sheet name would also be displayed.
So the appended table looks like this
Workbook Name | Sheet Name | NEED THIS Col1 | NEED THIS Col2 | NEED THIS Col3 (If available) | NEED THIS Col4 (If Available)

All the workbooks look exactly like the attached except sometimes the NEED THIS columns start on row 1 and sometimes they are lower. The NEED THIS Columns usually dont have headers and they arent in table format.

This is good info thank you. Im bad at explaining but yes I was trying to figure out if this was something I could easily put together with below avg VBA skills or if i had to go the paid route. Paid route seems to be it.