Pivot table + Macro

[cidfidou]

Hi all,

I am trying to create a macro in order to get a pivot table.
How can we do that when your number of rows and columns are not fixed?

Thanks for your help

 

[texasalynn]

Welcome to the board . . .

you will need to have a variable that looks at the number of rows, something like this

LR = ActiveSheet.UsedRange.Rows.Count
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"MC!R1C1:R" & LR & "C10").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable3", DefaultVersion:=xlPivotTableVersion10

where LR captures the number of rows, then you use that in the source data like I have shown.

 

[cidfidou]

Hi Texa,

Thanks for the prompt answer.. (that was incredibly fast!!)

I am sorry but I havent understood your reply. I am not programming in VBA and I am looking for a way to create a macro to create my pivot table. It works when i keep the same numbver of rows but as soon as I have more rows it doesnt work. When I recorded my macro I used ctrl + shift + arrow keys hoping it will tell excell how big my range is but it doesnt work. I have also tried by creating a Lits or using the shortcut ctrl + shift +* to select a range but it still doesnt work...

I would really appreciate if you could help me to do the above by using the built in fucntions!

Thanks again

 

[texasalynn]

post the "recorded" macro and I will show you how to incorporate what I posted.

 

[cidfidou]

Hi Texa,

thanks for your help but i really feel like a dummy. How can I show u the code of the macro?

 

[texasalynn]

from excel press Alt + F11
you will see module1, double click to select it. Press Ctrl+A to select all and copy, then in MrExcel post paste it.

or select Tools -- Macro -- Macros -- select your macro and click edit
copy all the macro (Ctrl+A - Copy)

When posting in MrExcel with code use code tags by placing at the beginning & end

[ code ] (without spaces)
paste your macro code
[ /code ] (without space)

 

[cidfidou]

Hi Again,

this is a summary of what i need to do.
Thanks again for your help. this is very nice of u

[Sub tp()
'
' tp Macro
' Macro recorded 19/01/2009 by xxxxxxxx
'
'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R15C2").CreatePivotTable TableDestination:= _
"'[Pivot macro example.xls]Sheet1'!R2C7", TableName:="PivotTable10", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable10").AddFields RowFields:="PN"
ActiveSheet.PivotTables("PivotTable10").PivotFields("qtity").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E7").Select
End Sub
]

 

[texasalynn]

try this

Sub tp()
'
' tp Macro
' Macro recorded 19/01/2009 by xxxxxxxx
'
LR = ActiveSheet.UsedRange.Rows.Count
'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R" & <b>LR</b> & "C2").CreatePivotTable TableDestination:= _
"'[Pivot macro example.xls]Sheet1'!R2C7", TableName:="PivotTable10", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables("PivotTable10").AddFields RowFields:="PN"
ActiveSheet.PivotTables("PivotTable10").PivotFields("qtity").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E7").Select
End Sub

 

[cidfidou]

Hi

It is working. Thanks a lot

Have a good day