Private Sub Workbook_Open()
Dim i As Integer
Application.AutoCorrect.AutoFillFormulasInLists = False
fPath = """" & Application.ActiveWorkbook.Path & """"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ESTIMATES (Sheet2) '''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Get name of each file
sName2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @fname """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 3).Resize(UBound(sName2)) = Application.Transpose(sName2)
' Get name of each file + extension
sNameExst2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @file """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 9).Resize(UBound(sNameExst2)) = Application.Transpose(sNameExst2)
' Get path of each file
sPath2 = Split(Replace(Replace(CreateObject("wscript.shell").exec("cmd /c forfiles /P " & fPath & " /S /M *-EST-*.xl?? /c ""cmd /c echo @path """).stdout.readall, Chr(32) & Chr(34), ""), Chr(34), ""), vbCrLf)
Sheet2.Cells(4, 10).Resize(UBound(sPath2)) = Application.Transpose(sPath2)
' Doc Number - Get cell value from estimate files
Worksheets(2).Activate
Range("K5").Select
Do Until IsEmpty(ActiveCell.Offset(, -8))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, -1).Value, Len(ActiveCell.Offset(, -1).Value) - Len(ActiveCell.Offset(, -2).Value)) & "[" & ActiveCell.Offset(, -2).Value & "]Summary'!$I$4"
ActiveCell.Offset(1, 0).Select
Loop
Worksheets(2).Activate
Range("A5").Select
Do Until IsEmpty(ActiveCell.Offset(, 2))
ActiveCell.Value = "=TEXT(" & ActiveCell.Offset(, 10) & "," & Chr(34) & "000" & Chr(34) & ")"
ActiveCell.Offset(1, 0).Select
Loop
' Revision - Get cell value from estimate files
Worksheets(2).Activate
Range("B5").Select
Do Until IsEmpty(ActiveCell.Offset(, 1))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 8).Value, Len(ActiveCell.Offset(, 8).Value) - Len(ActiveCell.Offset(, 7).Value)) & "[" & ActiveCell.Offset(, 7).Value & "]Summary'!$I$5"
ActiveCell.Offset(1, 0).Select
Loop
' Date - Get cell value from estimate files
Worksheets(2).Activate
Range("D5").Select
Do Until IsEmpty(ActiveCell.Offset(, -1))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 6).Value, Len(ActiveCell.Offset(, 6).Value) - Len(ActiveCell.Offset(, 5).Value)) & "[" & ActiveCell.Offset(, 5).Value & "]Summary'!$I$6"
ActiveCell.Offset(1, 0).Select
Loop
' Author - Get cell value from estimate files
Worksheets(2).Activate
Range("E5").Select
Do Until IsEmpty(ActiveCell.Offset(, -2))
ActiveCell.Value = "='" & Left(ActiveCell.Offset(, 5).Value, Len(ActiveCell.Offset(, 5).Value) - Len(ActiveCell.Offset(, 4).Value)) & "[" & ActiveCell.Offset(, 4).Value & "]Summary'!$G$7"
ActiveCell.Offset(1, 0).Select
Loop
End Sub
So to sum it up, the sheet is always called "Summary" in a project estimates excel workbook and the cell is always named "Estimate No" within this sheet. Please ignore the other worksheets if I left any in either workbook.