Create pivot table using macros

[nicolenwn]

Hi everyone(:

I'm having trouble creating pivot tables using macros.

First i tried recording it then running the exact same thing. It worked fine the first time but a week later when i tried it again the pivot table failed me. As i am using raw data as the data source, hence the number of rows changes everyday and therefore the datasource for the pivot table code cannot be fixed.

How do i do this?

ActiveWorkbook. PivotCaches.Add (SourceType:=xl Database, SourceData:= _
"'Raw Data'!R1C1:R511 C30").CreatePivotT able TableDestinatio n:= _
"'[Consolidated Report.xls]Table'!R22C3", TableName:="Piv otTable1", _
DefaultVersion: =xlPivotTableVe rsion10

Also highlighted in my raw data are exceptions. For example the record that is incompleted for customer A is highlighted in red. However after my pivot table is created when i click to view the records for Customer A, non of the trades are highlighted.

Please advise(: many thanks for all the help and effort.

Have a great day ahead and have a blast!

Cheers!
Nicole

[kadghar]

Well, here are some hints for not fixed ranges:

range(cells(1,1 ), cells(1,1).end( xldown))

will select from A1 all its way down until an empty cell

range(cells(1,1 ), cells(1,1).end( xlright))

will do the same but to the right

range(cells(1,1 ), cells(1,1).end( xlright).end(xl down))

yes... you can 'nest' them too.


HTH

[nicolenwn]

Well, here are some hints for not fixed ranges:

range(cells(1,1 ), cells(1,1).end( xldown))

will select from A1 all its way down until an empty cell

range(cells(1,1 ), cells(1,1).end( xlright))

will do the same but to the right

range(cells(1,1 ), cells(1,1).end( xlright).end(xl down))

yes... you can 'nest' them too.


HTH

Thanks for your input Kadghar! i'm still having problem with the exceptions part though, any idea on how to highlight the exceptions in the pivot table?

Also highlighted in my raw data are exceptions. For example the record that is incompleted for customer A is highlighted in red. However after my pivot table is created when i click to view the records for Customer A, non of the trades are highlighted.

Also, i've tried creating another pivot table using the same data, but it calls up error "1004" application-defined or object-defined error" any idea how i can fix this?

It shows the error but if i run it again it works, and sometimes it doesn't it's a little weird.

Best regards and thanks for all the help!
Attached is my pivot table codes

[nicolenwn]

Create pivot table using macros

Thanks for your input Kadghar! i'm still having problem with the exceptions part though, any idea on how to highlight the exceptions in the pivot table?

Also highlighted in my raw data are exceptions. For example the record that is incompleted for customer A is highlighted in red. However after my pivot table is created when i click to view the records for Customer A, non of the trades are highlighted.

Also, i've tried creating another pivot table using the same data, but it calls up error "1004" application-defined or object-defined error" any idea how i can fix this?

It shows the error but if i run it again it works, and sometimes it doesn't it's a little weird.

Best regards and thanks for all the help!
Attached is my pivot table codes

[kadghar]

Well, for exceptions, i think you should record the macro and watch how it's done, then modify it. I don't know quite well the sintax for pivot tables.

About the error, check that everytime you're creating the "PivotTable 9", why don't you put any other (autogenerated) name?

[nicolenwn]

hmm the exceptions are highlighted in the raw data before the pivot table is generated. Thus i think the pivot table has a standard format so it doesn't show the exceptions that is highlighted

hm with the autogenerated name, how do i go about doing this? as i am running the codes again now and now all of the pivot tables dont come out at all with the same error as "applicatio n-defined or object-defined error"

[kadghar]

About this exceptions, yes, that seems like a problem. you can make some IF/THEN checking the color or font or whatever you're using to highlight, and then highlight the table. But i dont think that'll be fast nor simple.

About the names, they're strings. put another string in your code where the name is. The name can be any string. Make a function that generates them in someway you dont repeat them (but It'll be useful to keep record of the ones you've generated)