Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2006
    Posts
    6

    Unhappy Unanswered: Need help in defining a variable for cell

    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)
    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

  2. #2
    Join Date
    Feb 2007
    Posts
    2
    I do it like this:

    Worksheets("Sheet1").Activate
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=ActiveSheet.Range(Cells(1, 1), Cells(8, 10)), _
    TableDestination:=Worksheets("Sheet2").Cells(3, 1), TableName:="PivotTable1", BackgroundQuery:=False

    Petr

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •