Case Study: VLookup data from Multiple Workbooks, and Apply Calculations in Master Workbook to update the Master File
Number Of Files: 4
Recon_Rough(Master File)
1) Merchandise Fullfilment Report
2) Merchandise Redemption Report
3) Orders Export File
Description:
Update Recon_Rough(Master File) from
1)Merchandise Fullfilment Report.xlsx
2) Merchandise Redemption Report.xlsx
3)Orders Export File
Sub Merchandise_12_09_2019()
Application.ScreenUpdating = False
Dim Recon_Template As Workbook
Dim wbk As Workbook
Dim x As Variant
Dim lrow As Integer
lrow = Cells(Rows.Count, 1).End(xlUp).Row
Set wbk = Workbooks.Open("D:\Recong\Merchandise_Fulfillment_12-09-2019.xlsx")
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("Q2:Q" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$K,10,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("R2:R" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$X,23,0)"
ActiveSheet.Range("R:R").Select
Selection.NumberFormat = "dd-mmm-yy"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("S2:S" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AC,28,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("T2:T" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AI,34,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("U2:U" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$N,13,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("V2:V" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$R,17,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("W2:W" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$S,18,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("X2:X" & lrow).Formula = "=$U2*$V2"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("Z2:Z" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$W,22,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AA2:AA" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$U,20,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AB2:AB" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$Q,16,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AC2:AC" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$T,19,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AD2:AD" & lrow).Formula = "=$AB2*0.25"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AE2:AE" & lrow).Formula = "=$AC2-$AD2"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AF2:AF" & lrow).Formula = "=$AA2+$AD2"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AG2:AG" & lrow).Formula = "=$AF2-$Z2"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AH2:AH" & lrow).Formula = "=AND(AF2>=X2,Y2=Z2)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AI2:AI" & lrow).Formula = "=Y2>X2"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1"). _
Range("AM2:AM" & lrow).Formula = "=VLOOKUP($D2,'D:\Recong\[" & "Merchandise_Fulfillment_12-09-2019.xlsx" & "]" & "Combined" & "'!$B:$AR,43,0)"
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("Merchandise_Fulfillment_12-09-2019.xlsx").Close SaveChanges:=False
Application.ScreenUpdating = True
Call Merchandise_Redemption
End Sub
-------------------------------------------------------------
Sub Merchandise_Redemption()
Application.ScreenUpdating = False
Dim wbk As Workbook
Dim lrow1 As Long
Dim Rng1 As Range
lrow1 = Cells(Rows.Count, 1).End(xlUp).Row - 1
Set wbk = Workbooks.Open("D:\Recong\Merchandise_Redemption_Report.xlsx")
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").AutoFilter Field:=17, Criteria1:="#N/A", Operator:=xlOr, Criteria2:="="
With ActiveSheet.AutoFilter.Range
Set Rng1 = Range("R" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row)
.Range(Rng1.Address & ":R" & lrow1).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,8,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,17,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 3).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,19,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 4).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,20,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 5).Formula = "=SUMPRODUCT(VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,{21,22},0))"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 7).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,28,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 8).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,28,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 9).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,15,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 10).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,14,0)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 12).Formula = "=$AB:$AB * 0.25"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 13).Formula = "=$AC:$AC-$AD:$AD"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 14).Formula = "=$AA:$AA+$AD:$AD"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 15).Formula = "=$AF:$AF-$Z:$Z"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 16).Formula = "=AND($AF:$AF>=$X:$X,$Y:$Y=$Z:$Z)"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 17).Formula = "=$Y:$Y>$Z:$Z"
.Range(Rng1.Address & ":R" & lrow1).Offset(0, 21).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "Merchandise_Redemption_Report.xlsx" & "]" & "Merchandise_Redemption_Report" & "'!$A:$AF,10,0)"
End With
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").ShowAllData
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("Merchandise_Redemption_Report.xlsx").Close SaveChanges:=False
Application.ScreenUpdating = True
Call Recon_Template2
End Sub
--------------------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Dim wbk As Workbook
Dim lrow2 As Long
Dim Rng1 As Range
lrow2 = Cells(Rows.Count, 1).End(xlUp).Row
Set wbk = Workbooks.Open("D:\Recong\orders_export_13_9_2019_12_0.xlsx")
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Activate
Worksheets("Sheet1").Range("A1").AutoFilter Field:=17, Criteria1:="#N/A", Operator:=xlOr, Criteria2:="="
With ActiveSheet.AutoFilter.Range
Set Rng1 = Range("Q" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row)
.Range(Rng1.Address & ":Q" & lrow2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "orders_export_13_9_2019_12_0" & "]" & "data" & "'!$F:$AV,18,0)"
.Range(Rng1.Address & ":Q" & lrow2).Offset(0, 2).Formula = "=VLOOKUP($D:$D,'D:\Recong\[" & "orders_export_13_9_2019_12_0" & "]" & "data" & "'!$F:$AV,37,0)"
End With
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").ShowAllData
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.Copy
Workbooks("Recon_Rough.xlsx").Worksheets("Sheet1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Workbooks("orders_export_13_9_2019_12_0.xlsx").Close SaveChanges:=False
Application.ScreenUpdating = True
