Create pivot Tabel Using dynamic range.

[jdixon12]

I have a macro that selects a range based on the contents of a cell. That part works. What I need is the macro to then use that range to create a pivot table. I can't figure out what to put in to tell the pivot table part to use the cells that have been selected. (I then want it to go an and select the next region that starts with the same cell contents)

Here is as close as I can get:

    Dim cell As Range
    Dim Data As Range
    For Each cell In Range("A1:A1500")
    If cell = "Code:  LUNCH" Then
    cell.Select
    ActiveCell.CurrentRegion.Select
    Set Data = ActiveRange
    
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "=Data").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Code:  LUNCH"), "Count of Code:  LUNCH", xlCount
    ActiveWorkbook.ShowPivotTableFieldList = False
    Application.CommandBars("PivotTable").Visible = False
    End If
    Set Data = Nothing
    Next cell
    End Sub

It doesn't like this part

Set Data = ActiveRange

but I don't know what it should be and couldn't find it with a search (most of this has been written by what I found in the forum here

Thank you in advance for the help

 

[SydneyGeek]

Change that to

ActiveCell.CurrentRegion.Select

That should fix it

Denis