Pivot Tables

Hello All,

I am new here, and fairly new to VB programming. Using VB I want to create a pivot table and for the life of me I can't find out how to do it. I have posted on other sites to no avail and was linked here to pose the same question.

I have a worksheet titled "Order" where I want all of the unique information in Column A in the Row section of the Pivot and all of the information in column F in the data section. What this should give me is a list of unique numbers from column A and the corresponding average of those numbers from column F below is an example

Column A - Column F
M1 - 100
M2 - 200
M3 - 150
M1 - 150
M1 - 125

Output would then be
M1 125
M2 200
M3 150

I hope that this makes sense. I saw somewhere that you can't do pivots in VB, but then it appeared to me that I saw an example of someone using it. Following the example though I couldn't replicate the function in my program.

Any assistance would be greatly appreciated

Try recording a macro when you create the pivot table manually. (Tools>Macro...Record New Macro...)


I just tried that with your sample data and here is what I got.

Please Login or Register  to view this content.

This gave me your expected result.

Thank you Norie for responding.

However, I am very new to this. Believe it or not, I had tried your idea and my scripts always came up with a ton of information that I couldn't translate into usable code.

I am using Hummingbird VB (just the form that I learned) and so even cutting and pasting your code into mine didn't work. I guess I have some questions. what is:

xlDatab
R1C1:R6C6

XL I am assuming is refering to Excel, but not sure what is meant by Datab

Then R1C1:R6C6 I would have thought to be some kind of range, but then why wouldn't it be A1:B4 or something smaller like that?

Sorry if these are stupid questions, but trying to understand the code behind what I am seeing. Thank you again for your help.

Regards,

Portree

Portree

I don't know why but when I posted the code it created spaces between
xlDatab and ase it should be xlDatabase.

Perhaps if you try that the code will work.

Did you try recording a macro yourself rather than pasting/using the code?

Hi Norie,

Yes, I did try and do my own macro, and it looked way different than what you posted and very different from the code that I am using. I have made the change to my code based on what you said and it gave me some errors (saying "Variable xldatabase is undefined") so I will play with it some more. If you have any further ideas I am all open. Thanks.

Portree

What version of Excel are you using?

I'm currently using 2000.

I used to use 97 and seem to remember that the code generated when recording making a pivot table was somehow different.

What code do you have?

I am running Excel 2002 or I think it is Office XP.

I have finally taken care of most of the errors. The final thing that I have is:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R6C6").CreatePivotTable TableDestination:=("H2"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Code"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlDataField
.Caption = "Average of Value"
.Function = xlAverage
End With

The bold text keeps giving me an error "2300 - Call Syntax Error" There is something about that my code doesn't like. Any thoughts there.

Appreciate your help Norie

The following worked fine for me:

Please Login or Register  to view this content.

All I changed was the TableDestination part - it's looking for a range rather than a string.

When I put in the range, I get a second error that Variable Range is not defined? I use the term in several other places, so I am not sure why it says that on this particular line.. Then I get the Call Syntax error as well.

Maybe it would help if I posted some other code. Just to give an idea of how I have written it so far.

order_pnumber = mid(xls.sheets("order").range("A" & count2).value,1,20)
order_pquantity = mid(xls.sheets("order").range("F" & count2).value,1,8)
us10_pnumber = mid(xls.sheets("US10").range("B" & count).value,1,20)
us10_pquantity = mid(xls.sheets("US10").range("E" & count).value,1,8)
us10_pdelv = mid(xls.sheets("us10").range("F" & count).value,1,3)
if order_pnumber = us10_pnumber then
xls.sheets("us10").range("E" & count).value = (order_pquantity + us10_pquantity)
xls.sheets("us10").range("F" & count).value = (us10_pdelv +1)
count2 = count2+1
count = 2
elseif order_pnumber <> us10_pnumber then
count = count+1
end if
if us10_pnumber = "" then
count2 = count2+1
count = 2
goto end_proc
end if
if order_pnumber = "" then
call close_file(xls)
end if


I did finally get the call error to go away, by adjusting the second line a little, but I still get Syntax Error for the second line. Here is how it looks now with line 2 being the problem line now:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R6C6").CreatePivotTable.TableDestination:=range("H2"), _
TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Code"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Value")
.Orientation = xlDataField
.Caption = "Average of Value"
.Function = xlAverage
End With

There shouldn't be a full stop between CreateTable and TableDestination.

I know, but when I take it out I get the error "Variable Range undefined" and the error Call syntax Error. Putting it in takes me down to only one error. I thought maybe adjusting for the specific type of program maybe would help.

Bump to see if there is an answer...

Well the dot definitely shouldn't be there.

I've no idea why you are getting that error.

It might be worth starting over in a new workbook, if it's possible, perhaps something's been corrupted.

Can you zip and attach the workbook?

I don't think it has anything to do with my Excel Workbook. This is the error I get when I am trying to compile the code. I haven't even gotten to the point where it will let me test this code out.

Like I said something might have become corrupt.

Why isn't it recognising Range and capitalizing it?

You don't have a variable named range do you?

Nope, I don't have a variable named range and as far as capitalizing, I have never seen it automatically capitalize in my code.

I appreciate your help. Maybe I am just doing it in some weird VB code that isn't cut out for this. Not sure if any of my code from the rest of the was any help or not.

Maybe I'm missing something.

Where are you actually using this code?

You mentioned Hummingbird VB in an earlier post but I thought you meant some sort of book/course.

Think of Excel. If you do a Macro then you can go in add edit it. Your not editing it in Excel, but a VB Editor. This is the same thing. Hummingbird is a program that we use, and the VB editor installed is what I use.

So, from here I can compile and run the script. However, with the error that I am getting it will not even let me do the compiling.

Right

Perhaps your application requires you to explicitly declare the range, including the worksheet name etc.

I noticed this sort of thing in your other code:

Please Login or Register  to view this content.

Perhaps you need something like that.

Agreed. That is where is was getting the stop point from:

"Sheet1!R1C1:R6C6").CreatePivotTable.TableDestination:=range("H2"), _
TableName:="PivotTable1"

Because this does take care of the Variable Range Undefined error that I was getting.

So, when I do that it gives me a general syntax error, which I take to mean that there is something in general that it doesn't like, I just have to find out what it means (some formatting or something).

Maybe try

Please Login or Register  to view this content.

Thanks Norie,

That is what I have been doing. Trying variations. When I think I get it right for one line the other line pops up giving me the Call Syntax error. Thanks for your help. I will keep playing with it and see if I can find out what is wrong.

Well, I have been trying for a while, and still no success. I have tried all kinds of variations and nothing is doing the trick.

Does anyone have any other ideas on what I can try. Right now I have to compare 500 lines of information with about 1500 lines. In pivot it takes just a few seconds, but in my code of comparing it takes it about 2 hours.

So, I am open to other ideas on a way to approach this.

Thanks

Portree

Bump

Quote: Then R1C1:R6C6 I would have thought to be some kind of range, but then why wouldn't it be A1:B4 or something smaller like that?

Apparently the syntax for the pivot table is different than for the rest of excel. Instead of "columnletter""rownumber" it's R"rownumber"C"columnnumber". So B3:D5 would be R3C2:R5C4.