Ok, for me, I like to just set up my pivot just the one time and then scoot on down the road. I just drop each month's new data at the end of my ongoing data sheet, select the sheet with the pivot table, and click a toollbar button that I have customized to run this macro and bang, I'm done and on to my next item. This code makes some assumptions about the data source and current region being one and the same. If that's not the case, you might want to tweak it.
Anyhow, here's the code to extend that Pivot source range. Note, it calls a function, which in turn calls another function, so there's three things here:
<font face=Courier New><SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Sub</SPAN> ExtendPivotSource()
<SPAN style="color:#007F00">' Written by Greg Truby, 2003</SPAN>
<SPAN style="color:#007F00">' Extends the source data range for a pivot table to be the CurrentRegion</SPAN>
<SPAN style="color:#007F00">' of the range that is currently the source data. [Allows for quickly</SPAN>
<SPAN style="color:#007F00">' updating reports based on pivot tables derived from datasets that get</SPAN>
<SPAN style="color:#007F00">' appended-to on a monthly basis.]</SPAN>
<SPAN style="color:#007F00">' Future improvement would be to just extend down the columns currently</SPAN>
<SPAN style="color:#007F00">' being used rather than taking the easy way out and using CurrentRegion.</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> pt <SPAN style="color:#00007F">As</SPAN> PivotTable, r <SPAN style="color:#00007F">As</SPAN> Range, a <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Set</SPAN> pt = ActiveSheet.PivotTables(1)
a = pt.SourceData
s = Left(a, InStr(1, a, "!"))
a = ConvertR1C1toA1(a)
<SPAN style="color:#00007F">Set</SPAN> r = Range(a)
<SPAN style="color:#00007F">Set</SPAN> r = r.CurrentRegion
a = s & r.Address(True, True, xlR1C1)
pt.SourceData = a
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>
<font face=Courier New>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Function</SPAN> ConvertR1C1toA1(strAddress <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">' Converts an address that is in R1C1 notation to A1 notation</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strSheet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strFirstCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strLastCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strCol1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strCol2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strRow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> intColon <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intCol1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intCol2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> intRow1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intRow2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">If</SPAN> InStr(1, strAddress, "!") <SPAN style="color:#00007F">Then</SPAN>
strSheet = Left(strAddress, InStr(1, strAddress, "!"))
strAddress = Replace(strAddress, strSheet, "", 1)
<SPAN style="color:#00007F">Else</SPAN>
strSheet = ""
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
intColon = InStr(1, strAddress, ":")
intCol1 = InStr(1, strAddress, "C")
<SPAN style="color:#00007F">If</SPAN> intColon <SPAN style="color:#00007F">Then</SPAN>
strCol1 = Mid(strAddress, intCol1 + 1, intColon - intCol1 - 1)
<SPAN style="color:#00007F">Else</SPAN>
strCol1 = Right(strAddress, Len(strAddress) - intCol1)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
strRow1 = Mid(strAddress, 2, intCol1 - 2)
strCol1 = ConvertNumToCol(Val(strCol1))
ConvertR1C1toA1 = strSheet & strCol1 & strRow1
<SPAN style="color:#00007F">If</SPAN> intColon = 0 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> Salida
intCol2 = InStr(intColon, strAddress, "C")
strCol2 = Mid(strAddress, intCol2 + 1, Len(strAddress) - intCol2)
strRow2 = Mid(strAddress, intColon + 2, intCol2 - intColon - 2)
strCol2 = ConvertNumToCol(Val(strCol2))
ConvertR1C1toA1 = ConvertR1C1toA1 & ":" & strCol2 & strRow2
Salida:
<SPAN style="color:#007F00">'""""""</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>
<font face=Courier New><SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN>
<SPAN style="color:#00007F">Function</SPAN> ConvertNumToCol(intNum <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> intTens <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, intOnes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> strTens <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strOnes <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
intTens = Int((intNum - 1) / 26)
<SPAN style="color:#00007F">If</SPAN> intTens > 0 <SPAN style="color:#00007F">Then</SPAN> strTens = Chr(64 + intTens) <SPAN style="color:#00007F">Else</SPAN> strTens = ""
intOnes = intNum Mod 26
<SPAN style="color:#00007F">If</SPAN> intOnes = 0 <SPAN style="color:#00007F">Then</SPAN> intOnes = 26
strOnes = Chr(64 + intOnes)
ConvertNumToCol = strTens & strOnes
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#007F00">'------------------------------------------------------------------------------</SPAN></FONT>