Windows 7 Professional x64 (Chrome)
Code
Sub UpdateWorkbooks()
'// WXLS [www.wxls.co.uk]
Dim parentFolder As String
Dim fileName As Variant
Dim myWorkbook As Excel.Workbook
Dim otherWorkbook As Excel.Workbook
Dim WS As Excel.Worksheet
MsgBox "Please select the folder containing the worker files.", vbInformation, "Update Records"
ThisWorkbook.Save
parentFolder = GetFolder(CurDir)
Set myWorkbook = ThisWorkbook
fileName = Dir(parentFolder & "\*.xls*")
For Each WS In ThisWorkbook.Sheets
If UCase(Left(WS.name, 9)) = "EMPLOYEE-" Then WS.Cells.ClearContents
Next WS
While Not fileName = vbNullString
If LCase(Mid(fileName, InStrRev(fileName, "."), 4)) = ".xls" Then
Set otherWorkbook = Workbooks.Open(parentFolder & "\" & fileName, False, True)
If UCase(Left(otherWorkbook.ActiveSheet.name, 9)) = "EMPLOYEE-" Then _
otherWorkbook.ActiveSheet.Range("A1").CurrentRegion.Copy Destination:= _
myWorkbook.Sheets(otherWorkbook.ActiveSheet.name).Range("A1")
otherWorkbook.Close False
Set otherWorkbook = Nothing
End If
fileName = Dir()
Wend
MsgBox "Update complete!", vbInformation, "Update Records"
End Sub
Display More
- Post shows first comment and variable declarations as being indented
- Double line breaks after Sub title, first comment, and after variable declarations