Re: Automatic Updating Of Master Worbook From Slave Workbooks
Hope this will be OK. I forgot some things :
Code
Private Sub Workbook_Open()
Dim Plage As Range, c As Range, Fich As String
Const myPath = "e:\donnees\daniel\mpfe\test\"
With Sheets("Not Updated Master Workbook")
' opens first workbook in the folder defined by "Const"
Fich = Dir(myPath & "*.xls")
'Loop into the folder
Do While Fich <> ""
' case workbook is "SlaveBook.xls"
If Fich = "SlaveBook.xls" Then
'set autofilter
.Range("E1:AA1").AutoFilter
' Set filter on column AA
Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
' set filter on column O
Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="M"
' define "Plage" as database offset 1
Set Plage = .Range("_filterdatabase").Offset(1)
' case at least one row is filtered out
If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
' assign filtered rows, first column only to Plage
Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
' open workbook
Workbooks.Open myPath & "SlaveBook.xls"
'Loops into Plage
For Each c In Plage
c.Offset(, 22) = Application.VLookup(c, [E:AA], 23, 0)
c.Offset(, 22).NumberFormat = "dd/mm/yy"
Next c
'Close File
Workbooks(Fich).Close False
End If
'reset autofilter
.Range("E1:AA1").AutoFilter
ElseIf Fich = "SlaveBook2.xls" Then
' same thing for the other workbook
.Range("E1:AA1").AutoFilter
Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=23, Criteria1:="="
Range(.[E1], .[A65536].End(xlUp)).AutoFilter Field:=10, Criteria1:="I"
Set Plage = .Range("_filterdatabase").Offset(1)
If Plage.Resize(, 1).SpecialCells(xlCellTypeVisible).Count > 1 Then
Set Plage = Plage.Resize(Plage.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
Workbooks.Open myPath & "SlaveBook2.xls"
For Each c In Plage
c.Offset(, 22) = Application.VLookup(c, [A:J], 10, 0)
c.Offset(, 22).NumberFormat = "dd/mm/yy"
Next c
Workbooks(Fich).Close False
End If
.Range("E1:AA1").AutoFilter
End If
Fich = Dir
Loop
End With
End Sub
Display More
If you have any trouble with it, please, post the workbooks again to refresh my mind.
Daniel