PDA

View Full Version : Solved: Adding Values in VBA but from a dynamic data query



jo15765
03-16-2012, 08:12 PM
I have 3 problems that are kicking my tail right now with a project I am working with. Here they are....

I want to be able to somehow copy the headers above each individual sales rep...the problem I have is I use ADO to pull in this data from a query so the row where the header info should be pasted would differ each time. Can someone provide a work around for that?

Also problem 2 that I have is that I have the Total Sale Column, but that is based off of that one Unique ID, I need to total the Sub Total for each Sales Rep, which again can change depending on that rep's sales. So I don't know how to program the Sales Total to equal the total of all total Sales? For example for Mike Smith his Sub Total would be =I2:I4

And last but not least problem 3 is I then need a Grand Total column for the Region, but only have it show Grand Total -- which of course would be the Sum of all the Sub Totals for the Sales Reps...but again since the data can change each time the query is refreshed I don't know how to code it.

p45cal
03-17-2012, 05:11 AM
No code yet, but you may not need it as this is so fast:
The green range is the source data for the orange pivot table:
7686
The pivot is laid out so (see next message)

p45cal
03-17-2012, 05:13 AM
the layout of the pivot table:
7687 It took les than a minute to produce.

p45cal
03-17-2012, 06:55 AM
and some code to run when that sheet is the active sheet:
Sub blah()
lr = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Range("A1:M" & lr)).CreatePivotTable(TableDestination:=Range("A" & lr + 2), TableName:="PT1", DefaultVersion:=xlPivotTableVersion10)
With .PivotFields("Sales Rep")
.Orientation = xlRowField
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Total Sale Amt")
.Orientation = xlDataField
.Caption = "Sum of Total Sale Amt"
.Function = xlSum
End With
End With
End Sub

jo15765
03-17-2012, 12:03 PM
I didn't even think of creating a pivot to pull the data in...That was a great idea!!! Thank you for that, it helps solve m problems 2 and 3.

Any idea's on how to fix problem 1? I need that header info that is currently only displaying in row 1 to display above each different sales reps information?

p45cal
03-17-2012, 01:19 PM
try this:
Sub Macro2()
lr = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveWorkbook.PivotCaches.add(SourceType:=xlDatabase, SourceData:=Range("A1:M" & lr)).CreatePivotTable(TableDestination:=Range("A" & lr + 2), TableName:="PT1", DefaultVersion:=xlPivotTableVersion10)
With .PivotFields("Sales Rep")
.Orientation = xlRowField
.PivotItems("(blank)").Visible = False
End With
With .PivotFields("Total Sale Amt")
.Orientation = xlDataField
.Caption = "Sum of Total Sale Amt"
.Function = xlSum
End With
End With
Set xxx = Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 2 To xxx.Count
xxx(i).Cells(1).Offset(-1).Resize(, 13).Value = Range("A1:M1").Value
Next i
End Sub

jo15765
03-17-2012, 02:46 PM
In your code what is the xxx? What do I need to set that as?


Set xxx = Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 2 To xxx.Count
xxx(i).Cells(1).Offset(-1).Resize(, 13).Value = Range("A1:M1").Value
Next i


If it helps this is all going to be displayed on one worksheet. The workbook will only contain that one worksheet (unless I use VBA to create a 2nd worksheet that is hidden to hold the pivot so it remains hidden from the average viewer)

p45cal
03-17-2012, 04:04 PM
In your code what is the xxx? What do I need to set that as?
Dim xxx as Areas

jo15765
03-17-2012, 06:42 PM
But how do I know the area's since the data is being pulled in via ADO query and could change any time it is re-run?

p45cal
03-18-2012, 04:12 AM
you don't need to. Just run the code on a sheet after the data import (there should be no pre-existing pivot table on that sheet), the SpecialCells bit determines where the areas are (it's the same as selecting column A, pressing F5, choosing Special.., then opting for Constants with all the check boxes checked, then clicking OK).

jo15765
03-19-2012, 07:01 AM
One last question and I will be set :) :) :) :)

From the pivot that is created, how do I base my sub total off of the total in the pivot? Meaning, since the data is constantly changing, how do I know the range to set my subtotal for per sales rep?

p45cal
03-19-2012, 10:53 AM
Are you saying that the cells with Sub Total in in column E are NOT produced by the query?

jo15765
03-21-2012, 10:53 AM
The phrase Sub Total is produced by the query. But the actual Amount is NOT produced by the query, that is correct.

p45cal
03-21-2012, 03:58 PM
Forget the pivot table, try the following, run only once on a set of data (perhaps try .clearcontents before refreshing the query) then run:
Sub blah()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set xxx = Range("i1:i" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 1 To xxx.Count
xxx(i).Cells(xxx(i).Cells.Count + 1).Value = Application.Sum(xxx(i))
GrandTotal = GrandTotal + Application.Sum(xxx(i))
If i = xxx.Count Then xxx(i).Cells(xxx(i).Cells.Count + 3).Value = GrandTotal
Next i

Set xxx = Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 2 To xxx.Count
xxx(i).Cells(1).Offset(-1).Resize(, 13).Value = Range("A1:M1").Value
Next i
End Sub It doesn't do a grand total though.

jo15765
03-21-2012, 06:27 PM
Alritey, I'll ditch the pivot and test this code in the a.m. and let you know. Thank you for the continued support and speedy responses!

p45cal
03-21-2012, 08:21 PM
It doesn't do a grand total though.Actually, it does.

jo15765
03-22-2012, 02:54 PM
One tweak to the above code, and it is PERFECT!

The row where the GRAND TOTAL amount is going to be entered...the $ amounts are being entered in column i and the words sub total are being entered in column D. Can you set the above code, to in column D of the row that is going to display the Grand total, say Grand Total?

Please, and many many thanks.

p45cal
03-22-2012, 10:53 PM
'Option Explicit
Sub blah()
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set xxx = Range("i1:i" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 1 To xxx.Count
xxx(i).Cells(xxx(i).Cells.Count + 1).Value = Application.Sum(xxx(i))
GrandTotal = GrandTotal + Application.Sum(xxx(i))
If i = xxx.Count Then
Set GTCell = xxx(i).Cells(xxx(i).Cells.Count + 3)
GTCell.Value = GrandTotal
GTCell.NumberFormat = xxx(i).Cells(xxx(i).Cells.Count + 1).NumberFormat
Cells(GTCell.Row, "D").Value = "Grand Total"
End If
Next i

Set xxx = Range("A1:A" & lr).SpecialCells(xlCellTypeConstants, 23).Areas
For i = 2 To xxx.Count
xxx(i).Cells(1).Offset(-1).Resize(, 13).Value = Range("A1:M1").Value
Next i
End Sub

jo15765
03-23-2012, 10:07 AM
Absolutely Perfect! Thank you, Thank you!