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

sanits591

Active Member
Joined
May 30, 2010
Messages
253
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!
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hmmm....something isn't adding up. :confused:

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?
 
Upvote 0
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!
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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...

Code:
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
 
Upvote 0
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!
 
Upvote 0
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.

Code:
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) :eeek:
 
Upvote 0
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!
 
Last edited:
Upvote 0
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. :)
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top