I use a "Refresh Pivot Table" Macro that sets the number of rows. Maybe
code from here can help you:
Sub PTableUpdate()
Dim LRow As Integer 'Last Row
Dim pt As PivotTable
Dim SD As String 'Source Data
Dim Sht As Worksheet
Dim Sht2 As Worksheet
Dim Sht3 As String
Application.StatusBar = "Updating Pivot Charts. Please wait."
Application.ScreenUpdating = False
Sht3 = ActiveSheet.Name
For Each Sht In ActiveWorkbook.Worksheets
For Each pt In Sht.PivotTables
SD = pt.SourceData
If InStr(1, SD, "'", vbTextCompare) 0 Then
Set Sht2 = Sheets(Mid(SD, 2, InStr(1, SD, "'!",
vbTextCompare) - 2))
Else
Set Sht2 = Sheets(Mid(SD, 1, InStr(1, SD, "!",
vbTextCompare) - 1))
End If
Sht2.Activate
LRow = FindLastCell.Row
SD = Left(SD, InStr(1, SD, ":", vbBinaryCompare)) & _
"R" & LRow & Right(SD, Len(SD) - InStrRev(SD, "C") + 1)
pt.SourceData = SD
pt.RefreshTable
Next
Next
Sheets(Sht3).Activate
ActiveWorkbook.ShowPivotTableFieldList = False
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

HTH
Die_Another_Day
ecctse wrote:
Hi,

I am trying to create a pivot tables using VBA with different number of rows
of source data. How can I supply the number of rows as a parameter to the
following method:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:="???"

It seems it doesn't like me to supply a string variable to the SourceData,
for example, SourceData:= strRange

Any other method to get around? Thanks.