CODEWITHSUNDEEP
excelvbaloopscharts
Reasonable
Reasonable

Reputation: 25

VBA program sub error; trying to set up conditional loop

This program is intended to be used to copy data from a pivot table on another sheet (varying number of rows for each data set). Each set of pasted data is used to create its own waterfall chart, for which I have templates already made on a different sheet.

There are a couple of issues I am having with this code. 1) For some reason, it no longer runs (I refactored the code from a macro) and gives me the error 'Compile Error: Sub or Function not defined' - I've tried making a new module and a new macro but to no avail 2) Also, I want to change the range that the chart graphs based on the size of the data set. Here's what I have currently hardcoded:

Selection.Formula = "=SERIES(,Sheet5!R8C1:R17C1,Sheet5!R8C4:R17C4,1)"

Complete Code below:

Sub WF_New_Sheet()

Dim copyFrom As Range
Dim wS As Worksheet 'use as current worksheet
Dim cht As Chart

'Paste and format data
Set wS = Sheets("Pivot 1")
copyFrom = wSRange("C82:D90")
Set wS = Sheets.Add(After:=Worksheets.Count)
wS.Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Selection.Columns.AutoFit
Application.CutCopyMode = False
Range("A9", Range("B" & Rows.Count).End(xlUp).Address).sort Key1:=[b9], _
Order1:=xlAscending, Header:=xlNo 'sorts in 2 lines
Range("A8").Value = "Total"
Range("B8").Value = "=SUM(R[1]C:R[9]C)"
Dim rNum As Integer: rNum = Range("A9", Range("B" & Rows.Count).End(xlUp).Address).Rows.Count

'Paste data template and chart
copyFrom = Sheets("Sheet4").Range("D2:G15") 'sheet 4 is hardcoded and contains templates
wS.Range("D6").Resize(copyFrom.Rows.Count).Value = copyFrom.Value
Sheets("Sheet4").ChartObjects("Chart 1").Activate
Application.CutCopyMode = False
ActiveChart.ChartArea.Copy
wS.Range("I7").Select
ActiveSheet.Paste
ActiveSheet.ChartObjects("Chart 1").Activate

'Set appropriate ranges for chart data; format data for display
ActiveChart.SeriesCollection(2).Select
Selection.Formula = "=SERIES(,Sheet5!R8C1:R17C1,Sheet5!R8C5:R17C5,2)" 'How to make this dynamic?
ActiveChart.SeriesCollection(1).Select
Selection.Formula = "=SERIES(,Sheet5!R8C1:R17C1,Sheet5!R8C4:R17C4,1)" 'How to make this dynamic?
Range("B8").Value = "=SUM(R[1]C:R[9]C)*-1"
With Range("b9", "b17")
.Value = Evaluate(.Address & "*" & -1)
End With

End Sub

*edit code fixed to include sub and end sub

Upvotes: 1

Views: 105

Answers (1)

Reasonable
Reasonable

Reputation: 25

Figured out how to adjust the chart size. Added these lines:

    Dim rowStart As Integer: rowStart = InputBox("Please enter starting row of your dataset.")
    Dim rowEnd As Integer: rowEnd = InputBox("Please enter ending row of your dataset.")

    Set copyFrom = Sheets("Pivot 1").Range(Sheets("Pivot 1").Cells(rowStart, colOne), Sheets("Pivot 1").Cells(rowEnd, colOne))
    Set wS = Sheets.Add
    wS.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
    wS.Range("A9").Resize(copyFrom.Rows.Count, copyFrom.Columns.Count).Value = copyFrom.Value
    Set copyFrom = Sheets("Pivot 1").Range(Sheets("Pivot 1").Cells(rowStart, colTwo), Sheets("Pivot 1").Cells(rowEnd, colTwo))
    wS.Range("B9").Resize(copyFrom.Rows.Count, copyFrom.Columns.Count).Value = copyFrom.Value

Upvotes: 1

Related Questions