Need help with extracting data from CSV files in a folder

Hi guys, I am trying to extract the data from CSV files placed in a folder (Location 1). In this regard, I generated a MACRO but the code is not working with other files.

Please note that the path to the folder is: C:\Users\Universities\Cornell\Plastics Circular Economy\4. Modelling\1. Solar Irradiance Data\Idaho\Location 1

The Excel file in which I want to import data is: Idaho.xlsm

I want to combine all the data from the source files into a master sheet, similar to the one we can have using the Data>Data Extraction>Folders>Combine & Load.

I have the following code:

Please Login or Register  to view this content.

Hello nomanrazasial and Welcome to Excel Forum.
If you think that you might be interested in using Get & Transform (aka Power Query) instead of VBA to accomplish this then the linked video may help: https://www.youtube.com/watch?v=pMQK1Zcc9jw&t=319s