ok, I know someone always says this was answered on another thread, but I have looked and looked for weeks and tried so many combinations my eyes are bleeding. I'm not very good at this, and my efforts to adapt other code has not been fruitful.
I have 8 identical pieces of equipment with 38 sensors each. I want to be able to cycle through each available bit of data and select each column that has a "1" or "2" etc for the first character. (someday it would also be nice to search for temp vs pressure, but baby steps, yeah?) The message i get is below, and I highlighted the buggy code in red/bold/underline
Sub add_column_field()
'add the column field to the pivot table.
'the field is determined by the button text that calls the macro. it can be 1 - 8 based on our equipment number
'source:Use VBA Macro Buttons to Add/Remove Pivot Table Fields Instantly! (on youtube)
Dim PT As PivotTable
Dim PF As PivotField
Dim sField As String '1, 2, 3, 4, etc as text
Dim shp As Shape
Dim wb As Workbook
On Error GoTo ErrHandler
Set wb = ActiveWorkbook
Set PT = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text
PT.RefreshTable
'add field that button was clicked for
For Each PF In PT.PivotFields
If InStr(1, Left(PF.Name, 1), sField) > 0 Or PF.Name = "date" Then PT.adddatafield PF, PF, xlSum Else 'PF.Orientation = xlHidden
Next
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
Resume Next
End Sub
I have 8 identical pieces of equipment with 38 sensors each. I want to be able to cycle through each available bit of data and select each column that has a "1" or "2" etc for the first character. (someday it would also be nice to search for temp vs pressure, but baby steps, yeah?) The message i get is below, and I highlighted the buggy code in red/bold/underline
Sub add_column_field()
'add the column field to the pivot table.
'the field is determined by the button text that calls the macro. it can be 1 - 8 based on our equipment number
'source:Use VBA Macro Buttons to Add/Remove Pivot Table Fields Instantly! (on youtube)
Dim PT As PivotTable
Dim PF As PivotField
Dim sField As String '1, 2, 3, 4, etc as text
Dim shp As Shape
Dim wb As Workbook
On Error GoTo ErrHandler
Set wb = ActiveWorkbook
Set PT = ActiveSheet.PivotTables(1)
Set shp = ActiveSheet.Shapes(Application.Caller)
sField = shp.TextFrame.Characters.Text
PT.RefreshTable
'add field that button was clicked for
For Each PF In PT.PivotFields
If InStr(1, Left(PF.Name, 1), sField) > 0 Or PF.Name = "date" Then PT.adddatafield PF, PF, xlSum Else 'PF.Orientation = xlHidden
Next
ErrHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error"
Resume Next
End Sub
