Results 1 to 3 of 3

Thread: Range selection

  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Range selection

    Hi there,
    How can I change the line which I marked
    <<<??>>> "Sheet1!R1C1:R12C2", so that the pivot table will include the whole range as I have selected in row 1.
    This is a recorded macro which will be used all the time , and the data range will change all the time.

    Range("A1:b1", Range("a1:b1").End(xlDown)).Select
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
    ' <<<??>>> "Sheet1!R1C1:R12C2", TableDestination:="", TableName:="PivotTable1"
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="model"
    ActiveSheet.PivotTables("PivotTable1").PivotFields ("model").Orientation = _
    xlDataField
    End Sub

    Thanks once again.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Range selection

    Rene,

    If your database resides in a sheet without any any other data, the following approach is probably the best :

    Give the data a name with
    Sheets("Sheet1").UsedRange.Name = "DataList"

    then use that name for the source data for the pivot table
    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= "DataList" etc

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Apr 2001
    Location
    SOUTH AFRICA
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Range selection

    Thanks Andrew,
    This works perfectly. I named my range like this:
    =offset(sheet1!$a$1,0,0,COUNTA(SHEET1!$A:$A),8) and used the name in the coding as you suggested.

Posting Permissions

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