Pivot Charts

JessyVal

New Member
Joined
Feb 23, 2021
Messages
30
Office Version
  1. 365
Hi guys,

I want to create rules/filters for charts/pivot charts with an excel sheet. The table is built to be populated with information from a user form but at the starting point it is blank, no information.

I cannot developed pivot charts and filters needed while the table is empty? Is there a way to do this? See below pivot chart based on beds.

1614176894341.png
1614176963504.png


Also when the charts are developed I need them to update automatically after each new entry. Each entry is completed using a user form.

I hope this makes sense. Please help if you can, thank you so much.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Using VBA, you can create pivot tables and charts, updating them when the source data changes:

pivotandchart.PNG


VBA Code:
' UserForm
Private Sub CommandButton1_Click()
Dim NewRange$, drange As Range
pn = [a1]
Set drange = [c2].CurrentRegion
NewRange = ActiveSheet.Name & "!" & drange.Address(ReferenceStyle:=xlR1C1)
If WorksheetFunction.CountBlank(drange.Rows(1)) > 0 Then
    MsgBox "Heading problems..."
    Exit Sub
End If
ActiveSheet.PivotTables(pn).ChangePivotCache ThisWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, SourceData:=NewRange)
ActiveSheet.PivotTables(pn).RefreshTable
End Sub

VBA Code:
Public pn$

Sub Table_and_Chart()
Dim pt As PivotTable, ch As Shape
pn = [a1]
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet7!R2C3:R" & Split(Range("c2").CurrentRegion.Address, "$")(4) & "C6", _
Version:=6).CreatePivotTable TableDestination:= _
"Sheet7!R2C8", TableName:=pn, DefaultVersion:=6
Sheets("Sheet7").Activate
Set pt = ActiveSheet.PivotTables(pn)
With pt.PivotFields("Month")
    .Orientation = xlPageField
    .Position = 1
End With
With pt.PivotFields("Person")
    .Orientation = xlRowField
    .Position = 1
End With
With pt.PivotFields("Region")
    .Orientation = xlColumnField
    .Position = 1
End With
pt.AddDataField pt.PivotFields("Amount"), "Sum of Amount", xlSum
pt.TableStyle2 = "PivotStyleLight19"
Set ch = ActiveSheet.Shapes.AddChart2(201, xlColumnClustered)
ch.Chart.SetSourceData Range(ActiveSheet.Name & "!" & pt.TableRange1.Address)
ch.Chart.PivotLayout.PivotTable.PivotFields("Region").PivotItems("West").Visible = 0
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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