Answered by:
PivotTables&Macros
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"
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
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



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 "foreveraltering 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 SubAlso, 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 
For the range,
ActiveChart.SetSourceData Source:=Sheets("Sheet1").RANGE("A2:B7"), PlotBy:= _
xlColumnscould be
With Sheets("Sheet1")
ActiveChart.SetSourceData _
Source:=.Range(.Range("A2"), .Cells(Rows.Count, 2).End(xlUp)), _
PlotBy:=xlColumns
End WithAs for the formula, what formula does the code put into the worksheet? (Copy and paste the resulting formula....)
HTH, Bernie Edited by Bernie Deitrick, Excel MVP 20002010 Monday, September 19, 2011 3:26 PM

I put your new code in, but it didn't like it. Said, "Runtime 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 
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 
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 
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 
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 

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 

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 


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