Macro to create Chart

[danay]

Good Morning

I have a daily report that I am trying to automate and the only section I am having trouble with is adding a column chart.

Some sample data that always is in cells A8:D12 is
State Current Evac Post
IA 12 23 45
KS 56 34 88
MO 23 12 55
NE 44 55 33

I would like to have a clustered cylinder chart and be sized from range A8:G29, the title of the chart for now would just be test and it needs to show data points. To make it more presentable I was just going to use one of the standard texture fill such as the paper bag or cork. I tried using the record macro but soon found out it only records the creation but no other changes such as size, font, ect..

Any help would be appreciated

 

[ZachD]

This is some code I've been using to create an area plot with a date axis.
You'll have to change it a bit, I hope it helps you get started.

Workbooks(filename).Worksheets(ws).Activate
Charts.Add
With ActiveChart
    .chartType = xlArea
    .SetSourceData Source:=Sheets(ws).Range("A2:B101")
    .Location Where:=xlLocationAsNewSheet, Name:="Chart"
    .HasLegend = True
    .HasTitle = True
    .ChartTitle = "Test"
 
    'Add first series
    series = 1
    With .SeriesCollection(series)
        .Name = "='" & ws & "'!R1C102"
        .XValues = "='" & ws & "'!R2C1: R367C1"
        .Values = "='" & ws & "'!R2C102:R367C102"
        .Interior.ColorIndex = 15
       .Border.LineStyle = xlNone
    End With
 
'Format x axis
    With .Axes(xlCategory)
        'No vertical gridlines
        .HasMajorGridlines = False
        .HasMinorGridlines = False
        'Format label
        .HasTitle = True
        With .AxisTitle
            .Top = 395
            .Left = 270
            .Characters.Text = "Month"
            .Font.Name = "Arial"
            .Font.FontStyle = "Bold"
            .Font.Size = 12
        End With
        .MajorTickMark = xlTickMarkNone
        With .TickLabels
            .NumberFormat = "mmm"
            .Font.ColorIndex = 2
        End With
        .CategoryType = xlTimeScale
        .BaseUnit = xlDays
        .MinimumScale = 1187
        .MaximumScale = 1552
        .MajorUnitScale = xlMonths
        .MajorUnit = 1
        .MinorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
    End With

Zach

 

[danay]

Thanks Zach

Below is what I have so far and it works except I am having problems resizing the chart. I would like the chart to be in the range of A8:G29, I have it in A8 but cannot seem to get the code right to get the height and width correct. And I need to change the source data and location to active sheet.

The second part but not as important is changing the background fill to one of the standard textures.

I am new to this and trying to learn so if my questions are very basic sorry.

Sub AddChart()
   Dim chtChart As Chart
   'Create a new chart.
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet48")
   With chtChart
    .ChartType = xlColumnClustered
    'Set the source data range for the chart.
    .SetSourceData Source:=Sheets("Sheet48").Range("A8:D12"), PlotBy:= _
        xlColumns
    .HasTitle = True
    .ApplyDataLabels xlDataLabelsShowValue
    .ChartTitle.Text = "test"
    With .Parent
        .Top = Range("A8").Top
        .Left = Range("A8").Left
      End With
   End With
End Sub

 

[ZachD]

Hey Danay,

I added a few things to your code.

1. the chart height and width are equal to the height and width of the range "A8:G29"
2. the chart is created in activeSheet. I had to make a new string variable for the sheet name. sheetName = ActiveSheet.Name
3. I wasnt sure if you wanted the chart of plot area, as a color or texture. So I added both. You should be able to change it accordingly.

Option Explicit
Dim chtChart As Chart
Dim sheetName As String
Sub AddChart()
 
sheetName = ActiveSheet.Name
 
'Create a new chart.
Set chtChart = Charts.Add
Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:=sheetName)
With chtChart
    .ChartType = xlColumnClustered
    'Set the source data range for the chart.
    .SetSourceData Source:=Sheets(sheetName).Range("A8:D12"), PlotBy:=xlColumns
    .HasTitle = True
    .ApplyDataLabels xlDataLabelsShowValue
    .ChartTitle.Text = "test"
    With .Parent
        .Top = Range("A8").Top
        .Left = Range("A8").Left
        .Width = Range("A8:G29").Width
        .Height = Range("A8:G29").Height
    End With
 
    .ChartArea.Format.Fill.ForeColor.SchemeColor = 17
    .PlotArea.Format.Fill.PresetTextured (msoTexturePapyrus)
    End With
End Sub

 

[danay]

Thanks alot I will mess around with it some more but I think that answers everything for now.