Macro to copy data from a set of excel files

[Admin]

Hi Sreejesh,

Welcome to ExcelFox !!

try this

Sub kTest()
    
    Dim wbkSource   As Workbook
    Dim wbkMaster   As Workbook
    Dim wksMaster   As Worksheet
    Dim Dest        As Range
    Dim FName       As String
    Dim i           As Long
    Dim k(), x
    
    '// User settings
    Const MyFolder = "E:\billing\bill\imported\"
    Const MyCells = "J4,B5,J10,C4"
    Const MasterSht = "Sheet1"
    'End
    
    If Len(Dir(MyFolder, vbDirectory)) Then
        Set wbkMaster = ThisWorkbook
        On Error Resume Next
        Set wksMaster = wbkMaster.Worksheets(MasterSht)
        If Err.Number <> 0 Then
            MsgBox "Master sheet '" & MasterSht & "' couldn't found", vbInformation
            Err.Clear
            Exit Sub
        End If
        On Error GoTo 0
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        Set Dest = wksMaster.Range("a" & wksMaster.Rows.Count).End(3)(2)
        x = Split(MyCells, ",")
        ReDim k(UBound(x))
        
        FName = Dir(MyFolder & "*.xls*")
        Do While FName <> vbNullString
            If FName <> wbkMaster.Name Then
                Set wbkSource = Workbooks.Open(MyFolder & FName, 0)
                With wbkSource.Worksheets(1)
                    For i = 0 To UBound(x)
                        k(i) = .Range(CStr(x(i))).Value
                    Next
                End With
                wbkSource.Close 0
                Set wbkSource = Nothing
                Dest.Resize(, UBound(x) + 1) = k
                Set Dest = Dest(2)
            End If
            FName = Dir()
        Loop
    End If
    With Application
        .ScreenUpdating = 1
        .DisplayAlerts = 1
    End With
    
End Sub