Sub ImportFiles3()
Dim xWS As Worksheet, xTWB As Workbook, DestSheet As Worksheet, Head As Range
Dim xStrAWBName As String, FolderName As String, sItem As String, Header As Long
Dim FolderPath As String, fldr As FileDialog, Lr As Long, LCD As Long, R As Long
Dim os As Long, LrS As Long, LCS As Long, FileName As String, DD As Long, FN2 As String
On Error Resume Next
Set xTWB = ThisWorkbook
Worksheets.Add
Set DestSheet = xTWB.ActiveSheet
Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
.Title = "Select a Folder containing files to merge"
.AllowMultiSelect = False
'.InitialFileName = Application.DefaultFilePath
If .Show <> -1 Then GoTo NextCode
sItem = .SelectedItems(1)
End With
NextCode:
FolderName = sItem
DD = InStrRev(FolderName, "\")
FN2 = Right(FolderName, Len(FolderName) - DD)
Set fldr = Nothing
FolderPath = FolderName & "\"
FileName = Dir(FolderPath & "*.xls*")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Do While FileName <> ""
Workbooks.Open FileName:=FolderPath & FileName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
If xWS.Name = "Master" Then
Lr = DestSheet.Range("A" & Rows.Count).End(xlUp).Row
LrS = xWS.Range("A" & Rows.Count).End(xlUp).Row
LCS = xWS.Cells(1, Columns.Count).End(xlToLeft).Column + 1
LCD = DestSheet.Cells(1, Columns.Count).End(xlToLeft).Column + 1
If Lr = 1 Then
Range(DestSheet.Cells([B]0[/B], 2), DestSheet.Cells(2, LCS)).Value = Range(xWS.Cells(1, 1), xWS.Cells(2, LCS - 1)).Value
DestSheet.Range("A1").Value = "FileName"
End If
Set Head = xWS.Range("A1")
For os = 0 To xWS.Cells(2, Columns.Count).End(xlToLeft).Column - 1
On Error Resume Next
Header = 0
Header = WorksheetFunction.Match(Head.Offset(0, os), DestSheet.Rows(1), 0)
On Error GoTo 0
If Header = 0 Then
DestSheet.Cells(1, LCD) = Head.Offset(0, os)
Header = LCD
LCD = LCD + 1
End If
If Lr = 1 Then
Range(DestSheet.Cells(Lr + 2, Header), DestSheet.Cells(Lr + LrS - 1, Header)).Value = Range(xWS.Cells(3, os + 1), xWS.Cells(LrS, os + 1)).Value
Else
Range(DestSheet.Cells(Lr + 1, Header), DestSheet.Cells(Lr + LrS - 2, Header)).Value = Range(xWS.Cells(3, os + 1), xWS.Cells(LrS, os + 1)).Value
End If
Next os
If Lr = 1 Then
Range(DestSheet.Cells(Lr + 2, 1), DestSheet.Cells(Lr + LrS - 1, 1)).Value = Left(FileName, Application.WorksheetFunction.Find(".", FileName) - 1)
Else
Range(DestSheet.Cells(Lr + 1, 1), DestSheet.Cells(Lr + LrS - 2, 1)).Value = Left(FileName, Application.WorksheetFunction.Find(".", FileName) - 1)
End If
End If
Next xWS
Workbooks(xStrAWBName).Close
FileName = Dir()
Loop
DestSheet.Activate
DestSheet.Name = "xStrAWBName"
Rows("2:2").Select
Selection.AutoFilter
ActiveWindow.FreezePanes = True
Columns("A:EA").EntireColumn.AutoFit
xTWB.Save
Sheets("xStrAWBName").Select
Sheets("xStrAWBName").Move
ActiveWorkbook.SaveAs FileName:=[U]FN2 & "[/U][B][U]MASTERSTACK"[/U][/B], FileFormat:=xlCSV, CreateBackup:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
MsgBox "MASTERS MERGED for " & xStrAWBName & vbLf & vbLf _
& "YOU CAN NOW FILTER AND KNOCK OUT SETS WITH NO PLANTING DATES!", vbInformation
End Sub