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.
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
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.
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
Any tips are greatly welcomed, thank you!