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:
It doesn't like this part
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
Here is as close as I can get:
Code:
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
Code:
Set Data = ActiveRange
Thank you in advance for the help