Hi
I am trying to write a VB Code to automate creation of Pivot Table
The code starts as below
-----------------------------------------------------------------------
Workbooks.Open "C:\Test1.xls"
Windows("Test1.xls").Activate
Refresh_Pivot
Application.DisplayAlerts = True
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"Sheet1!R1C1:R8C10").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
------------------------------------------------------------------------
If u notice the 6th line, I have hard coded the range as R1C1:R8C10 (assuming that I have 8 rows of data for creating Pivot Table)
However, the data might change and hence the no of rows to be selected for creation of Pivot Table shall also change. In such case I need to first count the number of rows I have, and then replace R8 with the Rn where n is the no of rows. Can somebody help me in developing this script
Thanks