Update Main Table with the help of table generated from Pivot Table in Excel

[sanits591]

Hi

I have been searching for a solution for the last few days, but unable to get anything relevant to it.

Data Available ( example)

1. One sheet with Name (Main Data) having the Data (with col A having Unique Values) with Validation in two columns (col D & Col E).
2. A pivot table is generated from this main table, and the values are simply the count and Sum in the "Values" section of the pivot table.
As soon as i double click on the field (on the values) a Data table, say "Generated Sheet" is generated consisting of values, now:

What i am looking for:

a) The column which are having data validation in the "Main Data" sheet should also have the data validation in the same columns (col D & col E) in the "Generated sheet", for the ease of operation or for amending the data.

b) If the data is edited in the "Generated Sheet" then it automatically changes the data in the "Main Data" sheet.
I would like to have this, because, merely editing a small portion on the "Generated sheet" shall be able to update the "Main Data".

I am not interested to touch "Main Data" directly for editing, as "Pivot Table" which is generated having so many filters, and requires to be changed for various analysis and can be drilled down to know the status of "generated sheet", and thereby the modification can be done there itself in "Generated Sheet" and which shall reflect in the "Main Data" sheet also.

I am not clear, moreover, i do not know how to take-off from here for the actions which i am seeking for.

Any assistance shall be appreciated for performing this task.

Thanks!

 

[Jerry Sullivan]

Hi santis591,

I have some code that applies number formatting from the source data to the drill-down's data range.

DataValidation (DV) is a little trickier because of limitations on referencing other sheets, but the code I have could probably be adapted to do that.

What type of DV criteria are you using in Columns D & E? (Are you using DV lists or some other type of Validation criteria?)

Do any of your DV criteria point to range references on the same sheet as the source data?

For the second part of your question (changes to the generated sheet update Main data), I think this could be accomplished, but there might need to be some reasonable constraints, otherwise handling all possible scenarios and exceptions might take a good deal of effort.

It's an interesting idea for a tool and I'd be glad to try writing some code to support it. I'll await your response to the questions above.

 

[sanits591]

Thanks for replying, the DV list consists of simply a named range, say "status" taken from another sheet, 'stages'.

Anticipatory thanks.

 

[Jerry Sullivan]

As a starting point, the code below will copy formats including DV from the top row of data of your datasource to the data rows of the drill-down detail table.

The set up requires copying code into three different code locations in the workbook, each identified below.

Paste into Sheet Code Module of the Worksheet with PivotTable

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
[COLOR="#008080"]'---If user double-clicks in PivotTable data, assigns a string reference to
'---  the Pivot Table's SourceData Property to Public string sSourceDataR1C1[/COLOR]

    On Error GoTo ResetPublicString
    With Target.PivotCell
        If .PivotCellType = xlPivotCellValue And _
            .PivotTable.PivotCache.SourceType = xlDatabase Then
                sSourceDataR1C1 = .PivotTable.SourceData
        End If
    End With
    Exit Sub
ResetPublicString:
    sSourceDataR1C1 = vbNullString
End Sub

Paste into ThisWorkbook Code Module

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject

    On Error Resume Next
    
    Set tblNew = Cells(1).ListObject
    If tblNew Is Nothing Then Exit Sub
    Call Format_PT_Detail(tblNew)
    Set tblNew = Nothing
End Sub

Paste into the top of a Standard Code Module

Option Explicit

Public sSourceDataR1C1 As String

Public Function Format_PT_Detail(tblNew As ListObject)
[COLOR="#008080"]'---Called by Workbook_NewSheet; Passes ShowDetail table object
'---Uses Pivot Table's SourceData Property stored in Public sSourceDataR1C1
'--- to read apply Formats in first row of SourceData to tblNew[/COLOR]
    

    Dim sSourceDataA1 As String

    If sSourceDataR1C1 = vbNullString Then Exit Function
    sSourceDataA1 = Application.ConvertFormula(sSourceDataR1C1, _
            xlR1C1, xlA1)
    Range(sSourceDataA1).Resize(1).Offset(1).Copy
    With tblNew.Range
        If .Rows.Count > 1 Then
            With .Offset(1).Resize(.Rows.Count - 1)
                .PasteSpecial Paste:=xlPasteValidation
                .PasteSpecial Paste:=xlPasteFormats [COLOR="#008080"] 'optional[/COLOR]
            End With
        End If
    End With
    sSourceDataR1C1 = vbNullString
End Function

Let's make sure this works for you before we explore the idea of linking changes to the Drill-down Table to the Main Data (Pivot's Data Source).

 

[sanits591]

Thanks for the effort done JS411, but it is not working with me to generate the DV on the sheet as soon as i double_click on the pivot table. I have done the pasting of code as mentioned above in your code.

Kindly help me to find out where it is going wrong, as no error comes after double_clicking on the pivot table, and the data is generated without any DV.

Thanks!

 

[Jerry Sullivan]

To help track down the problem, add a few messages to ensure each procedure is being run.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject
[B][COLOR="#0000CD"]    Msgbox "In NewSheet"[/COLOR][/B]
    On Error Resume Next

Public Function Format_PT_Detail(tblNew As ListObject)
    Dim sSourceDataA1 As String
[B][COLOR="#0000CD"]    Msgbox "In Format_PT_Detail"[/COLOR][/B]

 

[sanits591]

This particular code given below does not lead to any message.

Thanks!

Public Function Format_PT_Detail(tblNew As ListObject)
Dim sSourceDataA1 As String
Msgbox "In Format_PT_Detail"

 

[Jerry Sullivan]

Try one more...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
'---If user double-clicks in PivotTable data, assigns a string reference to
'---  the Pivot Table's SourceData Property to Public string sSourceDataR1C1
   [COLOR="#0000CD"][B] Msgbox "In BeforeDoubleClick"[/B][/COLOR]

Do you know how to use some of the debugging tools in the VB Editor such as Adding Breakpoints and Stepping through each line of code?

 

[sanits591]

The problem which is perceived after introducing this msgbox code for each code, for error checking lies with, which probably is unable to run after double clicking the pivot:

Public Function Format_PT_Detail(tblNew As ListObject)
Dim sSourceDataA1 As String
Msgbox "In Format_PT_Detail"

Thanks!

 

[Jerry Sullivan]

The error checking shouldn't stop the message box.

Do you have any Event Procedure Code in your Workbook (besides this code?)

Go to the Immediate Window of the VBE (Ctrl-G if it isn't visible). Enter:
?Application.EnableEvents
<hit enter after typing that statement>

If "False" is returned, that is the cause of the problem. In that case, enter in the Immediate Window:
Application.EnableEvents=True
<hit enter>
Then try double clicking the Pivot again.