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 .
- 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
- 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