Assist on creation of a code for merging files into one, and then merge data from sheets into one

[ramzes]

Hello all,

I don't have big knowledge and experience in VBA coding, but still learning.
I would like to ask you, VBA coders, for your kind assistance in creating a VBA code for my purpose.
I need data which is extracted day by day into one file.
So far I have found a code that merges files into one, in different sheets.
Now I need to copy from each sheet the specific range, into one. I've created a code with a macro recorder to copy columns, but its not working, with red marked the line which is showing as wrong in debug window.

Can anyone please advise?

Code for merging files:

Sub MergeExcelFiles()
    Dim fnameList, fnameCurFile As Variant
    Dim countFiles, countSheets As Integer
    Dim wksCurSheet As Worksheet
    Dim wbkCurBook, wbkSrcBook As Workbook
 
    fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)
 
    If (vbBoolean <> VarType(fnameList)) Then
 
        If (UBound(fnameList) > 0) Then
            countFiles = 0
            countSheets = 0
 
            Application.ScreenUpdating = False
            Application.Calculation = xlCalculationManual
 
            Set wbkCurBook = ActiveWorkbook
 
            For Each fnameCurFile In fnameList
                countFiles = countFiles + 1
 
                Set wbkSrcBook = Workbooks.Open(Filename:=fnameCurFile)
 
                For Each wksCurSheet In wbkSrcBook.Sheets
                    countSheets = countSheets + 1
                    wksCurSheet.Name = "New Name"
                    wksCurSheet.Copy after:=wbkCurBook.Sheets(wbkCurBook.Sheets.Count)
                Next
 
                wbkSrcBook.Close SaveChanges:=False
 
            Next
 
            Application.ScreenUpdating = True
            Application.Calculation = xlCalculationAutomatic
 
            MsgBox "Procesed " & countFiles & " files" & vbCrLf & "Merged " & countSheets & " worksheets", Title:="Merge Excel files"
        End If
 
    Else
        MsgBox "No files selected", Title:="Merge Excel files"
    End If
End Sub

This is the code i made (recording macro)

Sub Macro3copycol()
'
' Macro3copycol Macro
'


'
    Range("D13:N13").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .MergeCells = True
    End With
    Selection.UnMerge
    Range("D13").Select
    Selection.Copy
    Worksheets("Sheet1").Activate
    ActiveSheet.Paste
    Range("N5").Select
[COLOR=#ff0000]    Worksheets(ActiveSheet.Index + 1).Select[/COLOR]
    Range("I19").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveSheet.Previous.Select
    ActiveSheet.Paste
    Range("A4").Select
    ActiveSheet.Next.Select
    ActiveWindow.SelectedSheets.Visible = False
    Sheets("Sheet1").Select
End Sub