Hi team,
i am working with the merge all workbook into one master file.. require your help here to copy header at the top of Master sheet...
also it would be very great full if the code get converted easy and faster with the help of Ubound array .
Code
Sub Merger()
Dim wb As Workbook, sh As Workbook, fPath As String, fName As String
'Set sh = ThisWorkbook.Sheets(1)
Set sh = Workbooks.Add
fPath = ThisWorkbook.path 'If files are in a different directory than master, replace path here
If Right(fPath, 1) <> "\" Then fPath = fPath & "\" 'Make sure separator is on end of path
fName = Dir(fPath & "*Specific*net*.xl*") 'get all Excel files in directory
Application.ScreenUpdating = False
'fName = Dir(fPath & "*.xl*") 'get all Excel files in directory
Do
If fName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(fPath & fName)
With wb.Sheets(1)
If Application.CountA(.Rows(2)) > 0 Then
.UsedRange.Offset(1).Copy sh.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2)
End If
End With
wb.Close False
End If
fName = Dir
Loop While fName <> ""
sh.SaveAs Filename:=fPath & "Specific_Master_" & Format(Now, "dd-mmm-yyyy-(hh-mm-ss)") & ".xlsx"
sh.Close SaveChanges:=False
End Sub