Pivot Charts

JessyVal

New Member
Joined
Feb 23, 2021
Messages
13
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.
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,126
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,592
Messages
5,625,681
Members
416,127
Latest member
MALEPINZON

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
Top