[Solved] How to copy specific range cells from multiple sheets to one sheet?

by


I fully agree with guys who told you that none will write the code for you, but you are lucky that I have that already written and there may be other people looking for same info, so here it is.
Put the code to module in your master workbook and replace some data as commented:

Option Explicit

Sub GoThroughFilesAndCopyData()

Dim BrowseFolder As String
Dim FileItem As Object
Dim oFolder As Object
Dim FSO As Object
Dim shtWork As Worksheet
Dim lngRow As Long
Dim i As Long: i = 1
Dim strPath As String
Dim MasterSheet As Worksheet
Dim ChildSheet As Worksheet

Application.ScreenUpdating = False

    ' selecting the folder to look files in
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select the folder with child workbooks"
        .Show
        On Error Resume Next
        Err.Clear
        BrowseFolder = .SelectedItems(1)
        If Err.Number <> 0 Then
            MsgBox "You didn't select anything!"
            Application.ScreenUpdating = True
            Exit Sub
        End If
        On Error GoTo 0
    End With

    Set FSO = CreateObject("Scripting.FileSystemObject") ' creating filesystem object
    Set oFolder = FSO.getfolder(BrowseFolder) ' creating folder object

    Set MasterSheet = ThisWorkbook.Sheets("masterworksheet_name") 'replace masterworksheet_name with the name of your worksheet in master workbook

    For Each FileItem In oFolder.Files 'looking through each file

        If UCase(FileItem.Name) Like "*.XLS*" Then 'try open only excel files

            i = MasterSheet.Cells(Rows.Count, 1).End(xlUp).row + 1 ' find last not empty row and get a next one which is empty
            Workbooks.Open (BrowseFolder & Application.PathSeparator & FileItem.Name)
            Set ChildSheet = Workbooks(FileItem.Name).Sheets("worksheet_name") 'worksheet_name - replace with child sheet name

            With ChildSheet ' replace your_range_to_copy with the range on a child sheet you want to copy
                Range("your_range_to_copy").Copy Destination:=MasterSheet.Cells(i, column_number) 'i - is the number of last empty row, replace column_number - must be the column number of range to insert
                .Parent.Close SaveChanges:=False 'close child workbook without saving
            End With

        End If
    Next

    Application.ScreenUpdating = True

End Sub

solved How to copy specific range cells from multiple sheets to one sheet?