Compile Error: Sub or Function not defined when recording a macro to create a Pivot Table

[cmvirginia7]

Hello Everyone!

I just joined this forum and would love to hear your thoughts on an issue I'm having. I have been searching online and in an Excel Macro book, but cannot figure this out. I have a feeling there is a very simple solution that I'm just missing.

Software Version: Excel 2013, Windows 7

End Goal: I want to record a macro that creates a Pivot Table. I will start with a tab of raw data and then the macro will use a dynamic range to make the Pivot Table. I do not want to put the raw data into a table first because the data set is way too large. For the purposes of trying to figure out how to do this, I'm using a sample small data set right now.

My Code:

Sub Macro8()
'
' Macro8 Macro
'

'
    DataSheet ActiveSheet.Name
    Sheets.Add
    NewSheet ActiveSheet.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        DataSheet & "!R1C1:R9C4", Version:=xlPivotTableVersion15).CreatePivotTable _
        TableDestination:=NewSheet & "R3C1", TableName:="PivotTable5", DefaultVersion _
        :=xlPivotTableVersion15
    Sheets(NewSheet).Select
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Order Amt"), "Sum of Order Amt", xlSum
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Location")
        .Orientation = xlColumnField
        .Position = 1
    End With

Error Message:
I am receiving the following error message: Compile error: Sub or Function not defined. When I run the debugger, it highlights the very first line "Sub Macro8 ()".

If anyone has any thoughts, I would greatly appreciate it. I have been spending hours trying to figure out how to make this Pivot Table macro and it's driving me crazy. I'm also brand new to VBA and am reading the 101 Ready-To-Use Excel Macros book, but I can't find anything specific to my issue in there.

Thank you!

Warm regards,

Courtney

 

[AlphaFrog]

Missing an = sign.

DataSheet = ActiveSheet.Name


Without the equal sign, the VBA compiler thinks DataSheet is a missing sub or custom function.