none
run-time error '1004' with pivot tables in VBA RRS feed

  • Question

  • Hi all,

    I am using VBA code/ macros to create two pivot tables in do different worksheets using the "Data" sheet as their source.

    Macro - 1

    Sub source_Db()

    Dim sht As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String
    Dim objField As PivotFields
    Dim sh As Shape

    'Determine the data range you want to pivot
      SrcData = "Data" & "!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
      'Returns a String value that represents the range reference in the language of the macro.

    'Create a new worksheet
      Set sht = Sheets.Add


    'Where do you want Pivot Table to start?
      StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
      Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=SrcData)

    'Create Pivot table from Pivot Cache

      Set pvt = pvtCache.CreatePivotTable( _
        TableDestination:=StartPvt, _
        TableName:="Source")

     pvt.PivotFields("Message Source").Orientation = xlRowField
     pvt.PivotFields("Message Source").Orientation = xlDataField



    '  Set shp = ActiveSheet.Shapes.AddChart(xlLine)
    '  shp.Chart.SetSourceData Source:=ActiveSheet.PivotTables(1).TableRange1 ', PlotBy:=xlColumns
    '  shp.Chart.ShowAllFieldButtons = False
    '  shp.Chart.SeriesCollection(1).Smooth = True
    '  shp.Chart.HasTitle = True
    '  shp.Chart.HasTitle = False
    '
    '  shp.Chart.HasLegend = True
    '  shp.Chart.HasLegend = False
    '
    '  shp.Chart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(150, 0, 150)
    '  shp.Chart.Axes(xlValue).MinimumScale = 0
    '
    '  ActiveSheet.ChartObjects("Chart 1").Activate
    '    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3875, msoFalse, msoScaleFromTopLeft

    sht.Name = "Source Distribution"
    End Sub

    Macro - 2

    Sub buzz_trend()

    Dim sht As Worksheet
    Dim pvtCache As PivotCache
    Dim pvt As PivotTable
    Dim StartPvt As String
    Dim SrcData As String
    Dim objField As PivotFields
    Dim sh As Shape

    FinalRow = Sheets("Data").Cells(Application.Rows.Count, 1).End(xlUp).Row
        FinalCol = Sheets("Data").Cells(Application.Columns.Count).End(xlToLeft).Column



    'Determine the data range you want to pivot
      SrcData = Sheets("Data").Name & "!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
      'Returns a String value that represents the range reference in the language of the macro.
     'SrcData = Sheets(Data).Name & "!R1C1:R" & FinalRow & "C" & FinalCol
    'Create a new worksheet
      Set sht = Sheets.Add
     

    'Where do you want Pivot Table to start?
      StartPvt = sht.Name & "!" & sht.Range("A3").Address(ReferenceStyle:=xlR1C1)

    'Create Pivot Cache from Source Data
      Set pvtCache = ActiveWorkbook.PivotCaches.Create( _
        SourceType:=xlDatabase, _
        SourceData:=SrcData)

    'Create Pivot table from Pivot Cache
      Set pvt = pvtCache.CreatePivotTable( _
        TableDestination:=StartPvt, _
        TableName:="Buzz")
     
     pvt.PivotFields("Created On (Posted On)").Orientation = xlRowField
     pvt.PivotFields("Created On (Posted On)").Orientation = xlDataField
     
     pvt.PivotFields("Created On (Posted On)").LabelRange.Cells(2, 1).Group Start:=True, End:=True, _
     Periods:=Array(False, False, False, True, False, False, False)
     
      Set shp = ActiveSheet.Shapes.AddChart(xlLine)
      shp.Chart.SetSourceData Source:=ActiveSheet.PivotTables(1).TableRange1 ', PlotBy:=xlColumns
      shp.Chart.ShowAllFieldButtons = False
      shp.Chart.SeriesCollection(1).Smooth = True
      shp.Chart.HasTitle = True
      shp.Chart.HasTitle = False
      
      shp.Chart.HasLegend = True
      shp.Chart.HasLegend = False
      
      shp.Chart.SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(150, 0, 150)
      shp.Chart.Axes(xlValue).MinimumScale = 0
      
      ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveSheet.Shapes("Chart 1").ScaleWidth 1.3875, msoFalse, msoScaleFromTopLeft
      
    sht.Name = "Buzz Trend"
    End Sub

    When I run one macro, its works fine. But when I run the second macro, it gives me the following error -

    "Run time error 1004:

    This command requires at least two rows of source data. You cannot use the command on a selection in only one row.

    "

    Please help me with the problem. Thanks in advance.


    Tuesday, March 15, 2016 11:45 AM

All replies

  • Hi nix,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, March 16, 2016 7:36 AM
  • Since I do not have your test data I cannot run the code.

    Instead please open up VBA editor (Alt-F11), do all preparations and then set a breakpoint at the start of macro 2. (Select line FinalRow = Sheets ... and press F9. It'll turn red).

    Press Run (F5) and select buzz_trend()->run.

    When the macro halts at the breakpoint, press F8 to single-step through the code until the error shows. On what statement did the error occur?


    Best regards George

    Wednesday, March 16, 2016 3:06 PM
  • Hi, nix_13

    Could you help us figure out which line code cause this issue, and could you upload your Excel file On oneDrive? That will help us reproduce and resolve this issue.

    In addition as far as I know that when we create a PivotTable report based on a PivotCache object, the destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression.

    For more information, click here to refer about PivotCache.CreatePivotTable Method (Excel)

    Monday, March 21, 2016 9:26 AM