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

Option Explicit

Sub Open_My_Files()
Dim MyFile As String        ' File to be appeneded
Dim MyPath As String        ' Pathname to where CSV files are kept
Dim shS As Worksheet        ' Source worksheet
Dim shD As Worksheet        ' Destination worksheet

Application.DisplayAlerts = False

' Initialize Variable
MyPath = "C:\users\amirfar1\desktop\New folder"
MyFile = Dir(MyPath & "\*.csv")
' Loop through files
Do While MyFile <> ""
    Worksheets.Add after:=Sheets(Sheets.Count)
    Set shD = Sheets(Sheets.Count)
    shD.Name = Replace(MyFile, ".csv", "")
    
    Workbooks.Open MyPath & "\" & MyFile
    Set shS = ActiveWorkbook.Sheets(1)
    
    shS.Cells.Copy
    
    ActiveWorkbook.Close savechanges:=False
    shD.Range("A1").PasteSpecial xlPasteAll

    
    MyFile = Dir()
Loop
Application.DisplayAlerts = True

End Sub

Thank you,
Anthony

[[[Deleted duplicate]]]