Hi all, I have a spreadsheet that creates a new worksheet every time a form entry is made. The problem that I have at the moment is that the worksheet being generated from the template is not creating a functioning chart. When generated, the chart has no data but when the first entry is made, I want the chart to show it based on a pre-selected column.
Current behaviour is that the chart SEEMS to have the right references BUT I have to go in and edit the correct Category axis reference (to the same thing!) and then make an edit to any cell in the worksheet in order for the chart to function. VBA is below and happy to share the spreadsheet if it is any help!
Code
Private Sub OKButton_Click()
'Make Sheet1 active
Sheets("Direct Investments").Select
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("c:c")) + 7
'Transfer information
Cells(emptyRow, 3).Value = Borrower.Value
Cells(emptyRow, 5).Value = Ccy.Value
Cells(emptyRow, 6).Value = Region.Value
Cells(emptyRow, 7).Value = HQ.Value
Cells(emptyRow, 8).Value = DateSerial(Year.Value, Month.Value, Day.Value)
Cells(emptyRow, 9).Value = CreditClass.Value
Cells(emptyRow, 10).Value = Yield.Value / 100
Cells(emptyRow, 11).Value = YieldBasis.Value
Dim ws As Worksheet
Sheets("Template").Select
Sheets("Template").Copy After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ActiveSheet.Name = (emptyRow) + 9993 & " " & Left(Borrower.Value, 15)
ActiveSheet.ChartObjects(1).Activate
ActiveChart.Parent.Name = "Performance"
ActiveChart.Axes(1, 1).HasTitle = True
ActiveChart.Axes(1, 1).AxisTitle.Text = "Date" '1 = xlCategory, 1 = xlSecondary
With ActiveChart
Do Until .SeriesCollection.Count = 0
.SeriesCollection(1).Delete
Loop
End With
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("O13:O500")
.XValues = ActiveSheet.Range("k13:k500")
End With
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("l13:l500")
.XValues = ActiveSheet.Range("k13:k500")
End With
ActiveChart.SeriesCollection(1).AxisGroup = 2
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("O13:O500")
.XValues = ActiveSheet.Range("k13:k500")
End With
With ActiveChart.SeriesCollection.NewSeries
.Values = ActiveSheet.Range("l13:l500")
.XValues = ActiveSheet.Range("k13:k500")
End With
End Sub
Display More