I have a bit of code which will generate a pivot table on a new sheet placing the data as both the row field and data field (outputting a count on the # of occurrences of each description/word. The problem is that unless the data happens to select the header row the correct header is not grabbed and the count of the very first description (forgot to mention each cell is a qualitative description) is one less than it technically should be.
What I need the code to do instead is if the selected range does not include the top row (where the header lies) use the header from the first row, which would hopefully fix both issues. Hope I explained the scenario well enough, happy holidays!
What I need the code to do instead is if the selected range does not include the top row (where the header lies) use the header from the first row, which would hopefully fix both issues. Hope I explained the scenario well enough, happy holidays!
Code:
Sub DoAll()
Dim CloudData As Range
On Error Resume Next
'Asks user to specify which column of data they wish to summarize
Set CloudData = Application.InputBox("Please select a range with the incident information you wish to summarize.", _
"Specify Incident Information", Selection.Address, , , , , 8)
'Sub MakeTable()
Dim Pt As PivotTable
Dim strField As String
'Pass heading to a String variable
'*Need to make edit here most likely with some sort of if to use the first row heading
'if selected data does not include this. i.e. if selected range starts with second row
'or after.*
'strField = Selection.Cells(1, 1).Text
strField = CloudData.Cells(1, 1).Text
'Name the list range not using the xlDown because there exists
'the possibility of blanks in the column data.
'Range(Selection, Selection.End(xlDown)).Name = "Items"
CloudData.Name = "Items"
'Create the Pivot Table based off our named list range.
'TableDestination:="" will force it onto a new sheet
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:="=Items").CreatePivotTable TableDestination:="", _
TableName:="ItemList"
'Set a Pivot Table variable to our new Pivot Table
Set Pt = ActiveSheet.PivotTables("ItemList")
'Place the Pivot Table to start from A1 on the new sheet
ActiveSheet.PivotTableWizard TableDestination:=Cells(1, 1)
'Move the list heading to the Row Field
Pt.AddFields RowFields:=strField
'Move the list heading to the Data Field
Pt.PivotFields(strField).Orientation = xlDataField
ActiveSheet.Name = "FreqTable"
'Sorts frequency table descending. *has stopped working since I started tweaking this code I found
' With Range("A1", Cells(Rows.Count, 2).End(xlUp)).Resize(, 2)
' .Sort .Cells(1, 2), xlDescending
' End With
Call CreateCloud
