VBA code only works when I run it step by step (F8)

Question

I'm trying to create a macro to take a variable set of data, create a pivot table in a different sheet, then create a graph from that pivot table. It works fine when stepping through. However, when I try to click the run button or use a macro button, the code will create a new worksheet with an empty pivot table and no graph.

Here's the code.

Public Sub UnscheduledGraph()
    Dim wb As Workbook
    Dim ws As Worksheet

    Dim ws
    New As Worksheet

    Set wb = ThisWorkbook

    wb.Sheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
    Set ws = ActiveSheet

    'adds destination for pivot table
    Sheets("DataSheet").Activate
    Range("C5").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("A4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "DataSheet!R4C1:R24C40", Version:=6).CreatePivotTable TableDestination:=ws.Range("A1"), TableName:="Pivot " & Name, DefaultVersion:=6

    ActiveSheet.Next.Activate 

    With ActiveSheet.PivotTables("Pivot ")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("Pivot ").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("Pivot ").RepeatAllLabels xlRepeatLabels

    With ActiveSheet.PivotTables("Pivot ").PivotFields("Service Area")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivot ").PivotFields("Correlation ID")
        .Orientation = xlDataField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("Pivot ").PivotFields("Due Date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("Pivot ").PivotFields("Due Date").AutoGroup
    With ActiveSheet.PivotTables("Pivot ").PivotFields("Sum of Correlation ID")
        .Function = xlCount
    End With
    Range("B2").Select
    Selection.Group Start:=True, End:=True, By:=1, Periods:=Array(False, _
        False, False, True, False, False, False)

    'Graph portion

    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Shapes.AddChart2(286, xl3DColumnStacked).Select

    With ActiveChart
        .ClearToMatchStyle
        .ChartStyle = 294
        .SetElement (msoElementDataLabelShow)
        .ChartArea.Font.Color = RGB(255, 255, 255) ' color change
        .ChartArea.Font.Size = 16
    End With

    Stop

End Sub

See [this question](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Jan 13 '20 at 19:29
Your Active sheet is probably different than what your code expects when you run it. Get rid of all your selects/activates and refer to your workbooks/worksheets specifically. See the reference noted by @BigBen. — Ron Rosenfeld, Jan 13 '20 at 19:46
@RonRosenfeld How can I refer to a specific worksheet if it is a newly created worksheet? The pivot table I'm trying to make has a destination in the new worksheet — Paul Davidson, Jan 13 '20 at 20:17
You just set a worksheet variable to the new worksheet: `Dim pivotWS As Worksheet:Set pivotWS = Worksheets.Add` Now you can refer to it by `pivotWS` — Ron Rosenfeld, Jan 13 '20 at 20:47

VBA code only works when I run it step by step (F8)

0 Answers0