This is my stock macro for this task. You'll need to edit the sections in red so that it functions the way you want.
Option Explicit
Sub ConsolidateFiles()
'Open all Excel files in a specific folder and merge data into one master sheet (stacked)
'Moves imported files into another folder
'JBeaucaire (9/15/2009) (2007 compatible)
Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet
'Setup
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
Set wbkNew = ThisWorkbook
wbkNew.Activate
Sheets("Master").Activate 'sheet to build report onto, edit as needed
If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
Range("A2:BB" & Rows.Count).Clear
NR = 2
Else
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
End If
'Path and filename
OldDir = CurDir 'memorizes the user's current working path
fPath = "C:\My Documents\Files" 'path to files to import
fPathDone = "C:\My Documents\Files\Imported" 'path to put imported files when done
ChDir fPath
fName = Dir("*.xl*") 'filtering key, change to suit
'Import a sheet from found file
Do While Len(fName) > 0
'Open file
Set wbkOld = Workbooks.Open(fName)
'Find last row and copy data to report sheet
LR = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:A" & LR).EntireRow.Copy _
wbkNew.Sheets("Master").Range("A" & NR)
'close file
wbkOld.Close True
'Next row to import onto
NR = Range("A" & Rows.Count).End(xlUp).Row + 1
'move imported file to "imported" folder so it won't be reimported accidentally later
Name fPath & fName As fPathDone & fName
'ready next filename for import, then loop
fName = Dir
Loop
'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
'restores user's original working path
ChDir OldDir
End Sub
Just name your "Master" sheet for the import and put in the ROW1 headers. The macro will do the rest.
Bookmarks