I am new in learning excel VBA but I am facing a issue in a line of a code where I am using vlookup lookup value in is ” collection data” and lookup array in “Pivot” sheet. But where excel executes that line a pop up appears call update value from where “Pivot” is missing which includes my lookup array.

Line of code causing issue.

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Formula = "=IF(VLOOKUP($B2,Pivot!$A:$B,2,0)> Collection Details!$F2, Collection Details!$F2,VLOOKUP($B2,Pivot!$A:$B,2,0))"

I try to make two part of this subroutine and call other subroutine from that so “Update value sheets” can be update but issue remains the same.

All Sheets :=

I try to seeks answer for that problem on internet but I can’t find any answer related to this thing.

Excel VBA expert plz help me to solve my query.

Option Explicit

Sub pivot()

Dim count As Integer

ThisWorkbook.Worksheets(" Collection Details").Select

ThisWorkbook.Worksheets(" Collection Details").UsedRange.EntireColumn.AutoFit

ThisWorkbook.Worksheets(" Collection Details").Range("J2").Select

count = Range(Selection, Selection.End(xlDown)).count

ThisWorkbook.Worksheets(" Collection Details").Range(Selection, Selection.End(xlDown)).Value = "X00000002"

ThisWorkbook.Worksheets(" Collection Details").Range("I2:I" & count + 1).Value = "=TODAY()"

ThisWorkbook.Worksheets(" Collection Details").Range("I2:I" & count + 1).Select

Selection.Copy

Selection.PasteSpecial xlPasteValues

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Formula = "=IF(VLOOKUP($B2,Pivot!$A:$B,2,0)> Collection Details!$F2, Collection Details!$F2,VLOOKUP($B2,Pivot!$A:$B,2,0))"

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Select

ThisWorkbook.Sheets("CN-DN Data").Select

ThisWorkbook.Worksheets("CN-DN Data").Range("A1:A9").EntireRow.Delete

ThisWorkbook.Worksheets("CN-DN Data").UsedRange.EntireColumn.AutoFit

ThisWorkbook.Worksheets("CN-DN Data").Cells(1, 1).Select

Sheets("Pivot").Select

ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="CN-DN Data!R1C1:R1048576C15", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Pivot!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion15

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Sales Return Bill No").Orientation = xlRowField
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Sales Return Bill No").Position = 1

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").AddDataField ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Pending Amt"), "Sum of Pendig Amt", xlSum

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").Orientation = xlPageField
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").Position = 1

ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("From Sales Return").Visible = True
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("From Market Return").Visible = False
ThisWorkbook.Sheets("Pivot").PivotTables("PivotTable1").PivotFields("Narration").PivotItems("(blank)").Visible = False


End Sub

Sub Cn_Adjust()
'
'Cn_Adjust Macro
'
' Keyboard Shortcut: Ctrl Shift + Q
'
Dim count As Integer


ThisWorkbook.Worksheets(" Collection Details").Select

ThisWorkbook.Worksheets(" Collection Details").UsedRange.EntireColumn.AutoFit




Call pivot


ThisWorkbook.Worksheets(" Collection Details").Range("A1:O" & count + 2).AutoFilter Field:=7, Criteria1:="#N/A"

ThisWorkbook.Worksheets(" Collection Details").Range("A2:O" & count + 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete

ThisWorkbook.Worksheets(" Collection Details").AutoFilterMode = False

ThisWorkbook.Worksheets(" Collection Details").Range("G2:G" & count + 1).Select

Selection.Copy

Selection.PasteSpecial xlPasteValues


End Sub