Copy data from all files and consolidate

[boxsterman]

Hi all, I beleive I've seen something similar many times, but I can't get it to work. I have 77 files in one directory, I'd like to take the data from hidden sheet ("HC") and copy range ("A1:N21") from each file and paste it as a new table in a new master file. One sheet, data to fall in below the previous file's data.

Thank you.

 

[mumps]

Change the destination sheet name (in red) and folder path (in blue) where the files are saved, to suit your needs.

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wsDest As Workbook, wkbSource As Workbook
    Set wsDest = ThisWorkbook.Sheets("Sheet1")
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wsDest
            Sheets("HC").Range("A1:N21").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

 

[boxsterman]

Thank you, but I'm getting a runtime error 13, type mismatch at this line.

Set wsDest = ThisWorkbook.Sheets("Sheet1")

 

[mumps]

Oops!
Should be:

Sub CopyRange()
    Application.ScreenUpdating = False
    Dim wsDest As Worksheet, wkbSource As Workbook
    Set wsDest = ThisWorkbook.Sheets("Sheet1")
    Const strPath As String = "C:\Test\" 'change folder path to suit your needs
    ChDir strPath
    strExtension = Dir(strPath & "*.xlsx")
    Do While strExtension <> ""
        Set wkbSource = Workbooks.Open(strPath & strExtension)
        With wsDest
            Sheets("HC").Range("A1:N21").Copy .Cells(.Rows.Count, "A").End(xlUp).Offset(1)
        End With
        strExtension = Dir
    Loop
    Application.ScreenUpdating = True
End Sub