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

Davesexcel,

Step 1 is actually working it is just step 2 which I am having an issue with.

"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.
"

No, the code above accomplishes step 1 (import one or more files from a specific folder and paste them in their own sheet).

Basically I want to just learn the process so I can do the fine tuning and exact references myself. For learning purposes I would like column a values in the "OpenedFile" sheet to fill in the column A within the form on the "DestinationSheet"