none
PivotTables&Macros RRS feed

  • Question

  • I'm really new to pivot tables and these "codes" are confusing me.  Can anyone help me fix my codes so that when the number of rows changes with each query from Access, it will always include all the rows?  I've seen posts on "lastRow", but still too ignorant to fix my own.  My codes are below:

    RANGE("A1").Select
        ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
            "'SCRAP PERCENTAGES'!R1C1:R27C33", TableDestination:="", TableName:= _
            "PivotTable1"

    Tuesday, July 26, 2011 6:42 PM

Answers

  • You have two choices:

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    
    
    
    "'SCRAP PERCENTAGES'!R1C1:R" & Worksheets("SCRAP PERCENTAGES").Cells(Rows.Count, 1).End(xlUp).Row & _
    
    
    
     "C33", TableDestination:="", TableName:="PivotTable1"
    
    
    
    
    
    
    
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    
    
    
    Worksheets("SCRAP PERCENTAGES").Range("A1").CurrentRegion, TableDestination:="", _
    
    
    
    TableName:="PivotTable1"

     

    I prefer the second.

    The LastRow solution that you refer to would be

    Dim LastRow As Long

    LastRow = Worksheets("SCRAP PERCENTAGES").Cells(Rows.Count, 1).End(xlUp).Row

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _

    "'SCRAP PERCENTAGES'!R1C1:R" & LastRow & "C33", _

    TableDestination:="", TableName:="PivotTable1"



    HTH, Bernie

    • Marked as answer by shaydawn Wednesday, September 14, 2011 3:49 PM
    Wednesday, July 27, 2011 1:52 PM

All replies

  • You have two choices:

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    
    
    
    "'SCRAP PERCENTAGES'!R1C1:R" & Worksheets("SCRAP PERCENTAGES").Cells(Rows.Count, 1).End(xlUp).Row & _
    
    
    
     "C33", TableDestination:="", TableName:="PivotTable1"
    
    
    
    
    
    
    
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    
    
    
    Worksheets("SCRAP PERCENTAGES").Range("A1").CurrentRegion, TableDestination:="", _
    
    
    
    TableName:="PivotTable1"

     

    I prefer the second.

    The LastRow solution that you refer to would be

    Dim LastRow As Long

    LastRow = Worksheets("SCRAP PERCENTAGES").Cells(Rows.Count, 1).End(xlUp).Row

    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _

    "'SCRAP PERCENTAGES'!R1C1:R" & LastRow & "C33", _

    TableDestination:="", TableName:="PivotTable1"



    HTH, Bernie

    • Marked as answer by shaydawn Wednesday, September 14, 2011 3:49 PM
    Wednesday, July 27, 2011 1:52 PM
  • OMG, it worked like a dream....I'm so glad there are folks out there willing to help!  I used the second option, just copied and replaced my old codes...flawless!  Thanks again.
    Slongfellow
    Wednesday, July 27, 2011 2:06 PM
  • Great! Thanks for letting me know - can you mark my reply as the answer? - That let's everybody know that the thread is completed.

     


    HTH, Bernie
    Wednesday, July 27, 2011 2:11 PM
  • Hello, since you were so helpful the last time, I thought I'd ask for your help again...

    I have a different macro built for our Downtime and they need two charts.  I tried to use some of your "lingo" in this macro too, but can't figure out how to include the "forever-altering number of rows" issue in the Chart Data Source.  The macro is long, but I "bolded" the range that first got recorded in the codes below:


    Sub Downtime1()
    '
    ' Downtime1 Macro
    ' Macro recorded 08/25/11 by Shayla Longfellow
    '
    Application.ScreenUpdating = False
    '
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    "'UNSCHEDULED DOWNTIME'!R1C1:R" & Worksheets("UNSCHEDULED DOWNTIME").Cells(Rows.Count, 1).End(xlUp).Row & _
     "C9", TableDestination:="", TableName:="PivotTable1"
        ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Category"
        ActiveSheet.PivotTables("PivotTable1").PivotFields("Minutes").Orientation = _
            xlDataField
        With ActiveSheet.PivotTables("PivotTable1")
            .ColumnGrand = False
            .RowGrand = False
        End With
        With ActiveSheet.PageSetup
            .PrintTitleRows = ""
            .PrintTitleColumns = ""
        End With
        ActiveSheet.PageSetup.PrintArea = ""
        With ActiveSheet.PageSetup
            .LeftHeader = ""
            .CenterHeader = "&""MS Sans Serif,Bold""&18&F"
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
            .LeftMargin = Application.InchesToPoints(0.75)
            .RightMargin = Application.InchesToPoints(0.75)
            .TopMargin = Application.InchesToPoints(1)
            .BottomMargin = Application.InchesToPoints(1)
            .HeaderMargin = Application.InchesToPoints(0.5)
            .FooterMargin = Application.InchesToPoints(0.5)
            .PrintHeadings = False
            .PrintGridlines = False
            .PrintComments = xlPrintNoComments
            .PrintQuality = 600
            .CenterHorizontally = False
            .CenterVertically = False
            .Orientation = xlPortrait
            .Draft = False
            .PaperSize = xlPaperLetter
            .FirstPageNumber = xlAutomatic
            .Order = xlDownThenOver
            .BlackAndWhite = False
            .Zoom = 100
        End With
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").RANGE("A2:B7"), PlotBy:= _
            xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        With ActiveChart
            .HasTitle = False
            .Axes(xlCategory, xlPrimary).HasTitle = False
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "MINUTES"
        End With
        ActiveChart.HasLegend = False
        ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowValue, LegendKey:=False
        ActiveChart.Axes(xlCategory).Select
        Selection.TickLabels.Orientation = 45
        ActiveChart.ChartArea.Select
        ActiveSheet.Shapes("Chart 1").IncrementLeft -183#
        ActiveSheet.Shapes("Chart 1").IncrementTop -110
        ActiveSheet.Shapes("Chart 1").ScaleWidth 1.22, msoFalse, msoScaleFromTopLeft
        ActiveSheet.Shapes("Chart 1").ScaleHeight 1.14, msoFalse, msoScaleFromTopLeft
        ActiveWindow.Visible = False
        RANGE("A1").Select
        Charts.Add
        ActiveChart.ChartType = xlPie
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").RANGE("A2:B7"), PlotBy:= _
            xlColumns
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        ActiveChart.HasTitle = False
        ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowPercent, LegendKey:=True _
            , HasLeaderLines:=True
       
       
        ActiveSheet.Shapes("Chart 2").IncrementLeft -183#
        ActiveSheet.Shapes("Chart 2").IncrementTop 138#
        Windows("UNSCHEDULED DOWNTIME.xls").SmallScroll Down:=9
        ActiveSheet.Shapes("Chart 2").ScaleWidth 1.22, msoFalse, msoScaleFromTopLeft
        ActiveSheet.Shapes("Chart 2").ScaleHeight 1.12, msoFalse, msoScaleFromTopLeft
        ActiveChart.SeriesCollection(1).DataLabels.Select
        Selection.NumberFormat = "0.00%"
        Windows("UNSCHEDULED DOWNTIME.xls").SmallScroll Down:=9
        ActiveWindow.Visible = False
        Windows("UNSCHEDULED DOWNTIME.xls").Activate
        RANGE("A43").Select
        ActiveCell.FormulaR1C1 = "TOTAL PERCENT OF UNSCHEDULED DOWNTIME"
        RANGE("A44").Select
        ActiveCell.FormulaR1C1 = "TOTAL PERCENT OF UPTIME"
        RANGE("A43:E43").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = False
        End With
        Selection.Merge
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = True
        End With
        RANGE("A44:E44").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = False
        End With
        Selection.Merge
        With Selection
            .HorizontalAlignment = xlRight
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .ShrinkToFit = False
            .MergeCells = True
        End With
        RANGE("F43").Select
    '
     Dim GrnAvgLastRow As Long, i As Long
     Dim AvgFormulaRange As RANGE, SumFormulaRange As RANGE
     Dim ws As Worksheet
     
     Set ws = Sheets("UNSCHEDULED DOWNTIME")
     
     LastRow = ws.RANGE("A" & Rows.Count).End(xlUp).Row
     
      Set MyRange = ws.RANGE("A1:I" & LastRow)
     
      MyRange.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(8), _
     Replace:=False, PageBreaks:=False, SummaryBelowData:=True
     
      LastRow = ws.RANGE("A" & Rows.Count).End(xlUp).Row
     
      Set MyRange = ws.RANGE("A1:I" & LastRow)
     
      MyRange.Subtotal GroupBy:=6, Function:=xlAverage, TotalList:=Array(9) _
     , Replace:=False, PageBreaks:=False, SummaryBelowData:=True
     
      GrnAvgLastRow = ws.RANGE("F" & Rows.Count).End(xlUp).Row
     
      For i = GrnAvgLastRow - 1 To 2 Step -1
      If InStr(1, ws.RANGE("F" & i).Value, "Average", vbTextCompare) Then
       If AvgFormulaRange Is Nothing Then
        Set AvgFormulaRange = ws.RANGE("F" & i).Offset(, 3)
       Else
        Set AvgFormulaRange = Union(AvgFormulaRange, ws.RANGE("F" & i).Offset(, 3))
       End If
       If SumFormulaRange Is Nothing Then
        Set SumFormulaRange = ws.RANGE("H" & i - 1)
       Else
        Set SumFormulaRange = Union(SumFormulaRange, ws.RANGE("H" & i - 1))
       End If
      End If
     Next
     
     If Not AvgFormulaRange Is Nothing Then _
     ws.RANGE("F" & GrnAvgLastRow).Offset(, 3).Formula = "=Sum(" & AvgFormulaRange.Address & ")"

     If Not SumFormulaRange Is Nothing Then _
     ws.RANGE("F" & GrnAvgLastRow).Offset(, 2).Formula = "=Sum(" & SumFormulaRange.Address & ")"
       
            RANGE("A51").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(""grand average"",'UNSCHEDULED DOWNTIME'!R[-49]C[5]:R[1949]C[8],4,FALSE)"
        RANGE("B51").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*60)"
        RANGE("C51").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-1]*10%)"
        RANGE("D51").Select
        ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(""grand total"",'UNSCHEDULED DOWNTIME'!R[-49]C[-3]:R[1949]C[5],8,FALSE)"
        RANGE("E51").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/RC[-2])"
        RANGE("F51").Select
        ActiveCell.FormulaR1C1 = "=SUM(RC[-4]-RC[-2])/RC[-4]"
        RANGE("F43").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[8]C[-1])"
        RANGE("F44").Select
        ActiveCell.FormulaR1C1 = "=SUM(R[7]C)"
        RANGE("F43:F44").Select
        Selection.Style = "Percent"
        Selection.NumberFormat = "0.0%"
        Selection.NumberFormat = "0.00%"
        Selection.Font.Bold = True
        With Selection.Font
            .Name = "MS Sans Serif"
            .Size = 13.5
            .Strikethrough = False
            .Superscript = False
            .Subscript = False
            .OutlineFont = False
            .Shadow = False
            .Underline = xlUnderlineStyleNone
            .ColorIndex = 1
        End With
        RANGE("A1:I46").Select
        RANGE("I46").Activate
        ActiveSheet.PageSetup.PrintArea = "$A$1:$I$46"
        RANGE("A1").Select
    ActiveSheet.ChartObjects("Chart 2").Activate
        ActiveChart.PlotArea.Select
        With Selection.Border
            .Weight = xlThin
            .LineStyle = xlNone
        End With
        With Selection.Interior
            .ColorIndex = 2
            .PatternColorIndex = 1
            .Pattern = xlSolid
        End With
        ActiveWindow.Visible = False
        Windows("UNSCHEDULED DOWNTIME.xls").Activate
        RANGE("A1").Select
    Application.ScreenUpdating = True
    End Sub

     

    Also, you might know how to fix something in my second subtotal where I sometimes get an error if I have too much data? 

    "Thanking you (repeatedly) in anticipation of your help!" =)

     


    Slongfellow
    Monday, September 19, 2011 2:48 PM
  •  For the range,

       ActiveChart.SetSourceData Source:=Sheets("Sheet1").RANGE("A2:B7"), PlotBy:= _
            xlColumns

    could be

    With Sheets("Sheet1")
    ActiveChart.SetSourceData _
        Source:=.Range(.Range("A2"), .Cells(Rows.Count, 2).End(xlUp)), _
        PlotBy:=xlColumns
    End With

    As for the formula, what formula does the code put into the worksheet? (Copy and paste the resulting formula....)


    HTH, Bernie
    Monday, September 19, 2011 3:26 PM
  • I put your new code in, but it didn't like it.  Said, "Run-time error '1004':  Method 'Rows' of object '_Global' failed"

    As for the formula, we were changing the second subtotal to a "sum of the averages" in the "grand average" spot (instead of an average).  If I copy and paste the formula, then it won't change every time there is a different number of rows....will it?


    Slongfellow
    Monday, September 19, 2011 3:45 PM
  • Try changing

    Rows.Count

    to

    .Rows.Count

     

    If you copy and paste the formula, it will show me what you are trying to produce using this, and what happens when you have "too much data"

     If Not AvgFormulaRange Is Nothing Then _
     ws.RANGE("F" & GrnAvgLastRow).Offset(, 3).Formula = "=Sum(" & AvgFormulaRange.Address & ")"

     

     


    HTH, Bernie
    Monday, September 19, 2011 4:40 PM
  • Sorry =) 

    Adding the .Rows.Count worked!

    The formula for what I need is below, I think there is an extra step in there somewhere that I don't need.  Originally, we had both columns 8 & 9 adding, but only needed 9.  So, can one or the other code be deleted? 

    =SUM($I$55,$I$52,$I$49,$I$44,$I$41,$I$37,$I$31,$I$28,$I$24,$I$22,$I$20,$I$17,$I$14,$I$12,$I$9,$I$6,$I$3)


    Slongfellow
    Monday, September 19, 2011 5:02 PM
  • Your formula may be getting too long.

    If the cells being summed ($I$55,$I$52,etc.) themselves have SUM formulas, try this old accounting trick (from my old accountant who isn't really old, but I like the tip):

    =SUM(I1:I155)/2

    Does that return the value you want?


    HTH, Bernie
    Monday, September 19, 2011 5:15 PM
  • No, that doesn't give me what I need.  In my second subtotal, it is "averaging" our "available hours" (that our press is running) at each change in "shift".  The normal "grand average" gives me an average of the hours, I need it to give me a total of the hours.  I get my data from an Access Query, which duplicates the available hours for each category of downtime during the shift.  So if I have 4 categories of downtime, then I have 4 rows of data including the 8 hour shift.  So, I thought if I can have Excel "average" each shift, then give me a total of the averages for the period entered, I would have what I need.

    There might very well be a better way to get a total of the hours from my data, I just don't know what that'd be.

    Would a sample of the data help, I could wikisend it?


    Slongfellow
    Monday, September 19, 2011 5:33 PM
  • If you have labels in the rows with the averages, then you could try one other formula.

    =SUMIF(Column with labels,"Label text",Column with Average formulas)

    like

    =SUMIF(A:A,"Average Hours",I:I)


    HTH, Bernie
    Monday, September 19, 2011 5:37 PM
  • Sorry, again, but I don't know exactly what to replace? 

    Does this look right?

    =SUMIF(F:F,"Average",I:I)

    Column F is where the word "Average" shows up, and Column I is where the hours in the shift are.

    Also, Will just the word "average" work, because the actual text will include the shift number, like "1 Average" or "2 Average" or "3 Average"?


    Slongfellow
    Monday, September 19, 2011 6:01 PM
  • =SUMIF(F:F,"*Average*",I:I)

    will sum all the rows with average int he string in column F.


    HTH, Bernie
    Monday, September 19, 2011 6:27 PM
  • I like it...it's easier for me to understand.

    How much of the old verbage needs to be deleted?  I replaced:

    If Not AvgFormulaRange Is Nothing Then _
     ws.RANGE("F" & GrnAvgLastRow).Offset(, 3).Formula = "=Sum(" & AvgFormulaRange.Address & ")"

    with the new line, but it's red.  Did I delete too much?  Does it know where I want this sum (in the "grand average" row, 3rd column to the right)?

    Looks like you've got this about all figured out for me...I'm in your debt =)


    Slongfellow
    Monday, September 19, 2011 6:48 PM
  •     If Not AvgFormulaRange Is Nothing Then _
           ws.Range("F" & GrnAvgLastRow).Offset(, 3).Formula = _
           "=SUMIF(F:F,""*Average*"",I:I)"

     


    HTH, Bernie
    Tuesday, September 20, 2011 1:07 PM
  • Thanks Bernie,

    I'm getting a circular reference.   Maybe I will just have to make sure there isn't too much data at one time...thanks for all your help...and patience :) 


    Slongfellow
    Tuesday, September 20, 2011 1:40 PM
  • My bad. Your offset by 3 columns is putting the formuls into column I, so it creates the circular reference. Try

      If Not AvgFormulaRange Is Nothing Then _
           ws.Range("F" & GrnAvgLastRow).Offset(, 3).Formula = _
    "=SUMIF(F2:F" & GrnAvgLastRow - 1 & ",""*Average*"",I2:I" & GrnAvgLastRow - 1 & ")"


    HTH, Bernie
    Tuesday, September 20, 2011 2:59 PM