Pivot Table Code in Excel

tommycahir

Hi Once again

i have the below code to create a pivot table

when i hard code the range into the "SourceData:=" field i can create the pivot table ok. but i want to create the table so that it will only use the data selected in the Selection part. Does anybody have an idea on how to use the selection in the creating of the pivot table?

thx

Code:

Range("A2:M2").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    'create pivot table 
    
    With Selection 
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=[b]Selection[/b]).CreatePivotTable TableDestination:="", TableName:= _ 
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10 
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) 
    ActiveSheet.Cells(3, 1).Select 
    End With 
    
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("dept") 
        .Orientation = xlRowField 
        .Position = 1 
    End With 
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _ 
        "PivotTable2").PivotFields("dept"), "Count of dept", xlCount 
    Range("A5").Select 
    ActiveWorkbook.ShowPivotTableFieldList = True 
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("dept") 
        .Orientation = xlRowField 
        .Position = 1 
    ActiveWorkbook.ShowPivotTableFieldList = False

cyberbob

selection is a range object , you need selection.address

tommycahir

cool thx cyber bob that worked a treat

now i having another issue with the code

when i have the data filtered between a range it creates the pivot table on all the data from the start and not just the data in the selected range, is there anyway that i can select only the data in the filtered area and create the pivot table from that filtered data?

the code is attached below

any help would be welcome.

Sheets("UK").Select
    Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        Selection.Sort Key1:=Range("G3"), Order1:=xlAscending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End With
  
    With Selection
      ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=Selection.Address).CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    End With
    
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("dept")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("dept"), "Count of dept", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = True