VBA code to open all files in a directory and move data to destination sheet

Hello,

I am having trouble with a macro I am attempting to create.

I would like to use this macro to perform a few steps (listed below) to automate a task.

Step 1: I need to open all or one file in a specific directory and create a new sheet in the workbook for that file/s. (I believe I have a good handle on this step so far but any advice is welcomed)

Step 2: These file/s in this specific directory will always have the data I need in a specific cell in the sheet. I would like to move the data from the file I am opening to the "destination Sheet" and dump it in the correct column.

I cannot wrap my head around the correct code to move data that is in a cell that has yet to be created which is where I find myself. I have attached an example, any advice is welcomed

Code for step 1

Please Login or Register  to view this content.

Thank you,
Anthony

[[[Deleted duplicate]]]