PDA

View Full Version : Pivot Table data source



mdmackillop
04-11-2009, 03:07 AM
I have some code to create a pivot table from a Monthly sheet. Can I change the pivot table data source to another Monthly sheet while retaining the pivot table layout as modified by the user?

Shanmugam
04-11-2009, 07:48 AM
i do not know much about VB but sharing few of my knowledge in excel.

i think you can define a name from MS-Excel>insert>name>define>*create name* and use 'offset' formula to define data range for Pivot. In Pivot table wizard, instead of selecting the source range you can enter the defined 'name'.

give me more example how do you want to change the data source whether it is a same file with other sheet or with different file.

Shanmugam
04-11-2009, 07:56 AM
i have updated your attachment file. in Piv sheet, click to pivot table, right click, go to back, select source range and enter 'Val156' or 'Val157' for different output without disturbing pivot layout

mdmackillop
04-11-2009, 12:04 PM
Hi Shanmugan
Sound promising, but I'm not seeing what you mean by "go to back", so I'm missing the Range option. What version of Excel are you using?
For your information, there will be 20-30 sheets in the same book, similar to Val 156 and 157.

Zack Barresse
04-11-2009, 12:54 PM
Hi Malcolm,

You could use worksheet change events to handle it...

Private Const sDV As String = "B1"

Private Sub Worksheet_Activate()
Dim wb As Workbook, i As Long, sList As String, rDV As Range
Set wb = ThisWorkbook
For i = 1 To wb.Worksheets.Count
If wb.Worksheets(i).Visible = xlSheetVisible And _
Application.CountA(wb.Worksheets(i).Cells) <> 0 And _
wb.Worksheets(i).Name <> Me.Name Then
sList = sList & wb.Worksheets(i).Name & ","
End If
Next i
sList = Left(sList, Len(sList) - 1)
Set rDV = Me.Range(sDV)
Application.EnableEvents = False
rDV.Validation.Delete
rDV.Validation.Add Type:=xlValidateList, Formula1:=sList
rDV.Validation.InCellDropdown = True
rDV.Validation.IgnoreBlank = False
Call SetPivotDataRange(Me.PivotTables(1), rDV)
Application.EnableEvents = True
End Sub

Private Sub SetPivotDataRange(ByRef PT As PivotTable, ByRef rWS As Range)
Dim rData As Range, wks As Worksheet, iRow As Long, iCol As Long
Set wks = ThisWorkbook.Worksheets(rWS.Value)
iRow = Application.CountA(wks.Columns(1))
iCol = Application.CountA(wks.Rows(1))
Set rData = wks.Range("A1", wks.Cells(iRow, iCol))
PT.SourceData = "'" & wks.Name & "'!R1C1:R" & iRow & "C" & iCol
PT.RefreshTable
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range(sDV)) Is Nothing Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
Application.EnableEvents = False
Call SetPivotDataRange(Me.PivotTables(1), Target)
Application.EnableEvents = True
End Sub

mdmackillop
04-11-2009, 02:36 PM
Thanks Zack,
That will do nicely!
Regards
Malcolm

Shanmugam
04-13-2009, 04:16 AM
Zack,

I am getting error msg while compiling this as, "Invalid use of 'Me' keyword."

Zack Barresse
04-13-2009, 08:32 AM
Zack,

I am getting error msg while compiling this as, "Invalid use of 'Me' keyword."
The code goes into the worksheet module of the worksheet holding the pivot table "Piv".

Shanmugam
04-13-2009, 08:37 AM
Fantastic... superb.. working fine..