Sub Dependency_Updates()
'Imports the Status of the Dependent Asset
'Imports the Stage if still on a Tracker
Dim WB2 As Workbook 'Dependency Status.xlsm
Dim WS2 As Worksheet 'Sheets("Data")
Dim WS3 As Worksheet 'Sheets("Sign Off Vested")
Dim WB1 As Workbook 'This Workbook
Dim WS1 As Worksheet 'Legal Status
Dim CRge As Range
Dim PRge As Range
Dim VRge As Range
Dim Ret2 As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.DisplayStatusBar = False
ActiveSheet.DisplayPageBreaks = False
Application.CutCopyMode = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.AskToUpdateLinks = False
Set WB1 = ThisWorkbook
On Error Resume Next
Set WB2 = Workbooks("Dependency Status.xlsm")
If Err <> 0 Then
On Error GoTo 0
Set WB2 = Workbooks.Open(Filename:="File Path Removed", _
ReadOnly:=True)
End If
'Dependendcy Status Worksheets
Set WS3 = WB2.Sheets("Sign Off Vested")
Set WS2 = WB2.Sheets("Data")
'Tracker
Set WS1 = WB1.Sheets("Tracker")
ThisWorkbook.Activate
With Selection
Sheets("Tracker").Unprotect "vesting"
If ActiveSheet.AutoFilterMode Then
ActiveSheet.AutoFilterMode = False
Set myrange = WS1.Range("A8")
LastRow = WS1.Range("A1").CurrentRegion.Rows.Count
End If
End With
'*********
'CREATE VLOOKUP
'*********
Dim Vrange As Range
Dim VLastRow As Long
VLastRow = WS2.UsedRange.Rows(WS2.UsedRange.Rows.Count).Row
Set Vrange = WS2.Range("A2:A" & VLastRow)
A = "'[Dependency Updates.xlsm]Data'!$A$2:$I$"
B = A & VLastRow
C = "=IFERROR(Vlookup(" & "AM8," & B & ",3,FALSE" & "),"""")"
D = "=IFERROR(Vlookup(" & "AM8," & B & ",2,FALSE" & "),"""")"
Dim Vrow As Long
Vrow = WS1.Range("A1").CurrentRegion.Rows.Count - 1
WS1.Range("AN8:AN" & LastRow) = C
WS1.Range("AO8:AO" & LastRow) = D
Application.Calculation = xlCalculationAutomatic
WS1.Range("AN8:AO" & Vrow).Calculate
WS1.Range("AN8:AN" & Vrow).Value = WS1.Range("AN8:AN" & Vrow).Value
Sheets("Tracker").Range("AM5").Value = Format(Now, "mm/dd/yyyy HH:mm:ss")
Sheets("Tracker").Range("AM5").Value = Sheets("Tracker").Range("AM5").Value
Application.Calculation = xlCalculationAutomatic
Sheets("Tracker").Range("AN8:AO" & LastRow).Value = Sheets("Tracker").Range("AN8:AO" & LastRow).Value
Sheets("Tracker").Range("AN8:AO" & LastRow).Replace _
What:="0", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
WB2.Close Savechanges:=False
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.DisplayStatusBar = True
ActiveSheet.DisplayPageBreaks = False
Application.CutCopyMode = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.AskToUpdateLinks = True
Call ALL_ResetA
MsgBox "Dependency Data Imported"
End Sub