Programmatically change an existing Pivot Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello to everyone, wondering if someone has figure out how to change existing
Pivot Tables. Once a pivot table is been created the SourceData is read only.
Creating a new pivot table using VBA is too complex and time-expensive.

Any suggestions?
 
Once a pivot table is been created the SourceData is read only.

Naah.

I've managed to update SourceData and refresh my pivot tables
programatically (a whole set of 20+ pivot tables, actually). If I remember
correctly, the hoop I had to jump through was making sure the string I fed
SourceData was in the *exact* correct format. It's been a while since I
dealt with this, but below is (I hope) the pertinent code snippet. I hope
you might find it useful.

' Convert address of resized range back to R1C1 notation
strNewRange = Application.ConvertFormula( _
Formula:="=" & rng.Address, _
fromReferenceStyle:=xlA1, _
toReferenceStyle:=xlR1C1)
If Left(strNewRange, 1) = "=" Then strNewRange =
Mid(strNewRange, 2, Len(strNewRange) - 1)
On Error Resume Next

' Final touches to range-address-as-string
If Right(rng.Worksheet.Name, 1) = ")" Then
'Referenced sheet is a "copy", i.e., "Sheet1(2)" & needs
a leading aposthrophe (?)
strNewRange = "'" & rng.Worksheet.Name & "'!" &
strNewRange
Else
strNewRange = rng.Worksheet.Name & "!" & strNewRange
End If

' Apply range-address-as-string to pvt.SourceData
If pvT.SourceData <> strNewRange Then
pvT.SourceData = strNewRange
Else
pvT.RefreshTable
End If

Alternatively, create named data ranges (i.e., "Pivot1Data"), and use that
name for the Pivot.Datasource value. Programatically you can then resize the
named range when appropriate and refresh the table.
 
Hi

Not sure exactly what you mean here. Creating a PT has no effect upon the
source data in terms of its Read/Write status.

If you are referring to the data source growing in size, then instead of
using a fixed range when creating the table, create a Dynamic Named Range.

Insert>Name>Define>
Name MyData
Refers to =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))

In the PT source dialogue, enter =MyData
 
I reviewed your code and I used what I needed. Ultimately I used what Roger
suggested. Maintaining a Named Ranged seemed a lot easier to me. Its been
very long since I developed an interface in excel -it has been challenging
but I am enjoying the contract.

Thank you guys for your time and code, hopefully I may be able to help you
next time.
 
Back
Top