Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I detail Pivot Table data without creating a table (Excel2007) | Excel Discussion (Misc queries) | |||
How can I detail Pivot Table data without creating a table (Excel2007) | Excel Discussion (Misc queries) | |||
creating a report from a table, pivot table not suitable | Excel Worksheet Functions | |||
creating a pivot table from 4 pivot tables | Excel Worksheet Functions | |||
Creating Pivot Table with VBA | Excel Programming |