none
One folder having 10 or more file wan to merge into on excel worksheet vba RRS feed

  • Question

  • Hello!!

    i have one excel workbook in that total 15 coloumn is there. (this file is mater file)

    i have a team total 20 staff members everyday they were receiving call from students and updating the same excel. and at day end they are sending to me, i need to collect one by one.

    The process what i follow

    open the file selecting data from A2 till last fill cell, copy and pasting into mater sheet one by one.

    

    I need a vba code, please help me on this


    Regards, Prajakt Pande +971551388482

    Saturday, April 27, 2019 9:35 AM

Answers

  • You may try something like this...

    Sub CopyDataFromDailyFiles()
    Dim dwb As Workbook, wb As Workbook
    Dim dws As Worksheet, ws As Worksheet
    Dim fso As Object, fol As Object, fil As Object
    Dim strFolder As String
    Dim lr As Long
    
    Application.ScreenUpdating = False
    
    Set dwb = ThisWorkbook              'Master Workbook
    Set dws = dwb.Worksheets("Sheet1")  'Destination Sheet in the Master Workbook, change it as per your requirement
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select Folder which contains Daily Files."
        If .Show = -1 Then
            strFolder = .SelectedItems(1)
        Else
            MsgBox "You didn't select any folder.", vbExclamation
            Exit Sub
        End If
    End With
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fol = fso.getfolder(strFolder)
    
    For Each fil In fol.Files
        If fil.Name <> dwb.Name And Left(fil.Name, 1) <> "~" Then
            Set wb = Workbooks.Open(fil)
            Set ws = wb.Worksheets(1)       'Source Sheet on Source Workbook, assuming it is the first sheet in the workbook
            lr = ws.UsedRange.Rows.Count
            If lr > 1 Then ws.Range("A2:O" & lr).Copy dws.Range("A" & Rows.Count).End(3)(2)
            wb.Close False
        End If
        Set wb = Nothing
        Set ws = Nothing
    Next fil
    
    Set fso = Nothing
    Application.ScreenUpdating = True
    MsgBox "Task completed!", vbInformation
    End Sub


    Subodh Tiwari (Neeraj) sktneer

    • Marked as answer by Prajakt Pande Thursday, May 2, 2019 10:53 AM
    Sunday, April 28, 2019 7:55 PM