Copy & Paste VBA code - Events

[Zenwood]

I have a macro that pulls data from one worksheet, creates a new worksheet and adds a pivot table. I want to add an event handler to the pivot table of the new sheet.

How do I add the event handler code with VBA rather than copying a text file and pasting it to the Pivot Sheet?

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
 
'    Header 1
 
Range("B2:C2").Select
 
With Selection.Interior
     .Color = RGB(210, 230, 230)
End With
 
‘ Some more code…
 
End Sub

 

[Jerry Sullivan]

Hi Zenwood,

Run-time changes can be made to VBA modules using the VBProject.VBComponents property, but I don't recommend doing that since the code will only work if the user has set their macro settings to trust access to the VBA project object.

Trust Center > Macro Settings > check "Trust access to the VBA project object model"

A better alternative would be to add a new sheet by copying a template worksheet that includes your event code. The template sheet can be hidden if you prefer.

 

[Zenwood]

I got your suggestion to work. I learned excel has macro templates you can add to an existing worksheet. I learned you can add a pivot table to the imported sheet.

I ran into an error when the event code first accessed the pivot table. For some reason it did not like "Range("B2:C2").Select" or any part of my event macro with Range in it. So I added the code...

Application.EnableEvents = False

before the code created the pivot table and Application.EnableEvents = False after.

Now my worksheet works fine.

Thanks.