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!

 

[sanits591]

Thanks!, "True" is returned by using:
?Application.EnableEvents

 

[Jerry Sullivan]

Hmmm....something isn't adding up.

Right-click on the Sheet Tab of the Sheet that has the PivotTable. Then Click on View Code.....

Does Excel open up the VB Editor to the code module that has your Worksheet_BeforeDoubleClick procedure code?

 

[sanits591]

Yes, its reflecting over there also, I mean the code is very well pasted there itself, in fact for all the 3 places where is has been asked for.

Moreover, the message box MsgBox "In BeforeDoubleClick" would not have reflected, if the code was not there.

Thanks!

 

[Jerry Sullivan]

Yes, its reflecting over there also, I mean the code is very well pasted there itself, in fact for all the 3 places where is has been asked for.

Moreover, the message box MsgBox "In BeforeDoubleClick" would not have reflected, if the code was not there.

Thanks!

Ahh...I misunderstood that you were getting no MsgBox windows.

Which of the 3 messages are you seeing when you double click the Pivot?

 

[sanits591]

Jerry, only the message box which is not appearing on the screen after double clicking on the Pivot Table (rest of the 2 message box comes up as soon as we double click on the pivot table) is for:

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

Thanks

 

[Jerry Sullivan]

Ok...we are on zeroing in on the problem.

Replace the Workbook_NewSheet code in the ThisWorkbook module with this to gather some more clues...

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim tblNew As ListObject
    On Error Resume Next
    MsgBox "In Workbook_NewSheet"
    
    Set tblNew = Cells(1).ListObject
    If tblNew Is Nothing Then
        MsgBox "No table found on sheet: " & ActiveSheet.Name
        Exit Sub
    Else
       MsgBox "Now calling format_pt_detail with table: " & tblNew.Name
       
       Call Format_PT_Detail(tblNew)
       
    End If
    Set tblNew = Nothing
End Sub

 

[sanits591]

The last message which appears now, is "No table found on sheet: " & ActiveSheet.Name", i.e. name of the generated sheet after double clicking on the table.

A wild guess, do we need to capture the name of the newly generated sheet after double clicking on the pivot table? as every time on the double clicking, a new sheet is generated with an increment of 1 in the name of Sheet, i.e. first click, Sheet1, then clicking it again, shall generate Sheet2 and so on, which probably would be required to create a link of the Main Data with the newly generated sheet by double clicking on the pivot table to get the formulated DV on the generated sheet.

Thanks!

 

[Jerry Sullivan]

No, it shouldn't be necessary to use the sheet name itself since we should be able to get the Table object with this line.

Set tblNew = Cells(1).ListObject

Since the sheet reference is omitted, it will reference the ActiveSheet (the new sheet)
This works perfectly in my workbooks.

What version of Excel are you using? (I should have asked that earlier)

 

[sanits591]

I am using Excel 2010.

May i request you to please post the excel file, with some data, so that i can have a look on that for correcting myself.

Thanks!

 

[Jerry Sullivan]

I'm wrapping up for the night here. I've sent you a Private Message with my email address.

Will you please send me your file after removing any sensitive information?

This forum doesn't support the posting of attachments.

I'll be glad to send you my file as well and continue looking at this.... tomorrow.