GetPivotTable Data VBA

[Gregorys05]

HI guys, i am trying to get some VBA that will loop through every cell within range A1:E1 and pull back the value in a pivot table that relates to that date.

I have the below code that loops through each cell but i have no idea on how to bring back the data from the pivot table(Included below)

Ideally i would want the spreadsheet to look
01/07/2011 02/07/2011 03/07/2011
1 2 3

Any help would be great.

Sub LoopRow()
Dim Cell As Range
Dim T As String
Dim PT As PivotTable
Set PT = Worksheets("Sheet2").PivotTables(1)
For Each Cell In Range("A1:E1")
T = Cell.Address
    Select Case Cell.Value
    Case Is <= Date
       Cell.Offset(1, 0) = ?????????
  End Select
        
        Next Cell
End Sub

<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=130 border=0><COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 69pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=92 height=18></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 29pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=38></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Sum of figure</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Date</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Total</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>01/07/2011</TD><TD class=xl67 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>1</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>02/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>2</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>03/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>3</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>04/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>05/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>5</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>06/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>07/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>7</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>08/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>8</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>09/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>9</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>10/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>10</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>11/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>11</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>12/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>12</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>13/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>13</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>14/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>14</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>15/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>15</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>16/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>16</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>17/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>17</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>18/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>18</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>19/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>19</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>20/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>20</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>21/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>21</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>22/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>22</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>23/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>23</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>24/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>24</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>25/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>25</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>26/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>26</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>27/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>27</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>28/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>28</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>29/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>29</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>30/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>30</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18>31/07/2011</TD><TD class=xl68 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>31</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>Grand Total</TD><TD class=xl69 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>496</TD></TR></TBODY></TABLE>

 

[Jared_8878]

not sure if you have your heart set on vba,

but this can be done with match offset.

your pivot column date is in a2, pivot column total in b2

In d2 put your date you want to find
in e2 enter:

=OFFSET(A2,MATCH(D2,$A$3:$A$31,0),1)

and copy down to the rest of the dates you want to find being entered into column D