eric24g
New Member
- Joined
- Jun 2, 2022
- Messages
- 8
- Office Version
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
I created a VBA script that adds charts and slicers to my worksheet, but for some reason they don't work after adding them. Now, once the charts and slicers are added I would go and select on the chart, right click and click "select data" then click ok. Once I do that the slicers work with the chart. I did a macro record on this process and tried the code from there, but still didn't work. What am I doing wrong.
Below is my code for the slicer for vba:
Sub CreatSlicer()
Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim wS As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Set wS = Worksheets("Sheet2")
Set sourceSheet = Worksheets("worksheet1")
Workbooks("MyFile.xlsm").Worksheets("worksheet1").Activate
Set sc = wb.SlicerCaches.Add2(ActiveSheet.ListObjects("table1"), "slicer_name")
Set sl2 = sc.Slicers.Add(wS, , "slicer_name", "slicer_name")
Set rng = Range("W2:AC2")
sl2.Top = rng.Top
sl2.Left = rng.Left
sl2.Width = 150 'there are 72 points to an inch or 28.35 points to a centimeter
sl2.Height = 120
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate
End Sub
Below is my chart code:
Sub CreateLineChartWIP()
Dim sourceDataRange As Range
Dim sourceDataRange1 As Range
Dim sourceDataRange2 As Range
Dim sourceDataRange3 As Range
Dim sourceDataRange4 As Range
Dim wk As Worksheet
Dim myChart As chartObject
Set wS = Worksheets("Sheet2")
On Error Resume Next
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate
ActiveWorkbook.Charts(1).Delete
On Error GoTo 0
Set sourceDataRange1 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C105")
Set sourceDataRange2 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C208")
Set sourceDataRange3 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C367")
Set sourceDataRange4 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C470")
'create a new chart sheet and place it at the beginning of the destination workbook
Dim oChartSheet As Chart
' Set oChartSheet = destinationWorkbook.Charts.Add(destinationWorkbook.Sheets(1))
Set oChartSheet = wS.Shapes.AddChart.Chart
'set the properties for the chart
With oChartSheet
.SetSourceData Source:=sourceDataRange1
.SetSourceData Source:=sourceDataRange2
.SetSourceData Source:=sourceDataRange3
.SetSourceData Source:=sourceDataRange4
.ChartTitle.text = "Name Of Chart"
.ChartType = xlLine
.Axes(xlValue).TickLabels.NumberFormat = "0.0"
.Axes(xlValue).TickLabels.NumberFormat = "#,##0.0"
End Sub
Below is the code for when I used macro recording when selecting the chart, right clicking and selecting "select data" and pressing ok. Manual process worked, but running the code below didn't work for me. What am I doing wrong?
Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart WIP").Activate
ActiveChart.PlotArea.Select
End Sub
Thanks.
Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart").Activate
ActiveChart.PlotArea.Select
End Sub
Below is my code for the slicer for vba:
Sub CreatSlicer()
Dim rng As Range
Dim sl As Slicer
Dim sc As SlicerCache
Dim wS As Worksheet
Dim wb As Workbook
Set wb = ActiveWorkbook
Set wS = Worksheets("Sheet2")
Set sourceSheet = Worksheets("worksheet1")
Workbooks("MyFile.xlsm").Worksheets("worksheet1").Activate
Set sc = wb.SlicerCaches.Add2(ActiveSheet.ListObjects("table1"), "slicer_name")
Set sl2 = sc.Slicers.Add(wS, , "slicer_name", "slicer_name")
Set rng = Range("W2:AC2")
sl2.Top = rng.Top
sl2.Left = rng.Left
sl2.Width = 150 'there are 72 points to an inch or 28.35 points to a centimeter
sl2.Height = 120
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate
End Sub
Below is my chart code:
Sub CreateLineChartWIP()
Dim sourceDataRange As Range
Dim sourceDataRange1 As Range
Dim sourceDataRange2 As Range
Dim sourceDataRange3 As Range
Dim sourceDataRange4 As Range
Dim wk As Worksheet
Dim myChart As chartObject
Set wS = Worksheets("Sheet2")
On Error Resume Next
Workbooks("MyFile.xlsm").Worksheets("Sheet2").Activate
ActiveWorkbook.Charts(1).Delete
On Error GoTo 0
Set sourceDataRange1 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C105")
Set sourceDataRange2 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C208")
Set sourceDataRange3 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C367")
Set sourceDataRange4 = ActiveWorkbook.Worksheets("worksheet1").Range("B2:C470")
'create a new chart sheet and place it at the beginning of the destination workbook
Dim oChartSheet As Chart
' Set oChartSheet = destinationWorkbook.Charts.Add(destinationWorkbook.Sheets(1))
Set oChartSheet = wS.Shapes.AddChart.Chart
'set the properties for the chart
With oChartSheet
.SetSourceData Source:=sourceDataRange1
.SetSourceData Source:=sourceDataRange2
.SetSourceData Source:=sourceDataRange3
.SetSourceData Source:=sourceDataRange4
.ChartTitle.text = "Name Of Chart"
.ChartType = xlLine
.Axes(xlValue).TickLabels.NumberFormat = "0.0"
.Axes(xlValue).TickLabels.NumberFormat = "#,##0.0"
End Sub
Below is the code for when I used macro recording when selecting the chart, right clicking and selecting "select data" and pressing ok. Manual process worked, but running the code below didn't work for me. What am I doing wrong?
Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart WIP").Activate
ActiveChart.PlotArea.Select
End Sub
Thanks.
Sub SelectData2()
'
' SelectData2 Macro
'
'
ActiveSheet.ChartObjects("Line Chart").Activate
ActiveChart.PlotArea.Select
End Sub