convert code to import data from one sheet to new sheets

 Hi Halk.

My suggestion is that you have sheets with names like "dd@01-Mar-23" where the first part is the copied file name (e.g. from dd.xlsx) and after @ is the date of import. That way, you will know when the sheets were revised. If a file is missing on an attempted import, its sheet won't be deleted but left with an old date. New files will get a new dated sheet.

In the attached file, I've renamed your sheets like that and deleted the data. The macro below assumes that files will have a sheet called "sh"  (the variable ImpName) so the (crude) error  handling is triggered. You may need to comment out the first line starting Pth (in bold) and use your fixed path if files aren't in the same location as the .xlsm file:

Sub ConsolidateWbks()

    Dim Pth As String
    Dim fname As String
    Dim ImpName As String, wSh As Worksheet, Dn As String, NewName As String

    Application.ScreenUpdating = False
    On Error GoTo Prob
    ' state what sheet should be imported from data files
    ImpName = "sh"

'    Pth = "C:\TextFolder\2023\folders\"
    Pth = ThisWorkbook.Path & "\"

    fname = Dir(Pth & "*xls*")
    Do While Len(fname) > 0
        If fname <> ThisWorkbook.Name Then
        Debug.Print fname
        Workbooks.Open (Pth & fname)
            With Workbooks(fname)
                If .Worksheets(ImpName).Name = ImpName Then
                    Dn = ""
                    NewName = Left(fname, InStrRev(fname, ".") - 1)
                    'check if similar sheet exists
                    For Each sh In ThisWorkbook.Worksheets
                        If sh.Name <> "running" Then
                            If Left(sh.Name, InStrRev(sh.Name, "@") - 1) = NewName Then
                                ' if old found, copy new and delete old
                                .Worksheets(ImpName).Copy Before:=sh
                                Application.DisplayAlerts = False
                                sh.Delete
                                Application.DisplayAlerts = True
                                'set flag
                                Dn = "Replaced"
                                Exit For
                            End If
                        End If
                    Next sh
                    ' if not found, copy to end
                    If Dn <> "Replaced" Then .Worksheets(ImpName).Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                End If
                .Close , False
                'rename copied sheet with today's date
                ThisWorkbook.Worksheets(ImpName).Name = NewName & "@" & Format(Date, "dd-mmm-yy")
            End With
        End If
        fname = Dir
    Loop

Exit Sub

Prob:
'close opened book and tell user
Debug.Print Err.Number
ActiveWorkbook.Close False
MsgBox "Stopped since file " & fname & " does not contain a sheet called " & ImpName & " to import"

End Sub

Run the macro and it will replaced the empty sheets with data from your files. Add a new file (different name) to the import folder and it will create a new sheet.

Hope this works for you as a solution.