# VBA - How to use a VLookup to retrieve totals from a dynamic changing pivot table

I am pretty new at using VBA with Vlookups on Pivot tables so any help is appreciated here. I have a set of account numbers and names in a string in my first sheet that I need to retrieve totals for that I have in a pivot table in a separate sheet.

For example:

My first sheet "MOM YTD Totals" has the following content in cells A11:A600 (example is shorter but sometimes this list will be more than 600)

4-50000 Other Miscellaneous

4-50001 National

4-50002 Media

4-50003 Sponsors

and so on...

I would like to fill contents in B11:B600 with amount totals that match these account numbers and names however the pivot table ranges in size and I don't know how many columns it will have each month.

The second sheet "NW_pivot" has the pivot table I created starting at cell Q6 (Q -column with the account to lookup) and the Grand Total (last column in the pivot table) is the amount I want pulled into the first sheets column B.

and so on...

Does anyone have any tips on the easiest way to do this possibly? I have tried many variations with the code below but think I am just way off here and a vlookup should be used but I don't know how to even start with one in a pivot table so I apologize.

Sub MatchAccountTotals()

```
dim i As Long
dim j As Long
dim lastrow1 As Long
dim lastrow2 As Long
dim lastcol1 As Long
dim accountName As String
lastrow1=sheets("MOM YTD Totals").range("A" & Rows.Count).end(xlUp).row
For i=11 to lastrow1
accountName=sheets("MOM YTD Totals - All Entities").cells(i, "A").value
Sheets("NW_Pivot").Activate
lastrow2=Sheets("NW_Pivot").range("Q" & Rows.Count).end(xlUp).row
lastcol1=Sheets("NW_Pivot").Cells(6,columns.count).End(xlToLeft).Column
For j=6 To lastrow2
if sheets("NW_Pivot").cells(j,"Q").value=accountName
sheets("NW_Pivot").cells(j,"lastcol")).copy
sheets("MOM YTD Totals").activate
sheets("MOM YTD Totals").cells(i,"D").select
activesheet.paste
End If
Next j
application.CutCopyMode=False
Next i
sheets("MOM YTD Totals").Activate
sheets("MOM YTD Totals").range("A1").select
```

End Sub

Any tips are greatly welcomed, thank you!