Creating pivot table with VBA
 

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.

Creating pivot table with VBA
 

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.

Creating pivot table with VBA
 

Hi Die_Another_Day ,

Thank you very much for your speedy response. Your codes do make sense to me
& I will certainly try it out.

Many thanks,
Eric


"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.

Creating pivot table with VBA
 

Hi again Die_Another_Day,

You're a real champ. Your codes work perfectly. Just 1 more question: where
can I locate your FindLastRow object?

Very much appreciated & have a great weeekend.
Eric


"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.

Creating pivot table with VBA
 

Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

That's the function I use. Hope it helps

Die_Another_Day
ecctse wrote:
Hi again Die_Another_Day,

You're a real champ. Your codes work perfectly. Just 1 more question: where
can I locate your FindLastRow object?

Very much appreciated & have a great weeekend.
Eric


"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.

Creating pivot table with VBA
 

Hi Die_Another_Day,

Thanks again very much.
Eric


"Die_Another_Day" wrote:

Function FindLastCell() As Range
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
Set FindLastCell = Cells(LastRow, LastColumn)
Else
Set FindLastCell = Range("A1")
End If
End Function

That's the function I use. Hope it helps

Die_Another_Day
ecctse wrote:
Hi again Die_Another_Day,

You're a real champ. Your codes work perfectly. Just 1 more question: where
can I locate your FindLastRow object?

Very much appreciated & have a great weeekend.
Eric


"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.