从多个Excel文件中提取特定单元格并将其编译为一个Excel文件

I'm new to VBA, and I'd like to use it to do some difficult and arduous tasks. I have a large amount of Excel files with thousands of rows and several columns. I need to search, by row, and extract certain cells with specific strings. I've pieced together some functions and code and I have almost got it to work but I keep getting unexpected results like irrelevant data being extracted or it random errors because I don't understand VBA syntax super well. As a newbie to Excel, I'm at my wits end debugging this code and it still not giving me the results I need. My code thus far is as follows:I'm new to VBA, and I'd like to use it to do so