VBA: using range to cycle through pivot items

Joe_L

New Member
Joined
Mar 10, 2011
Messages
14
Hi everyone - I currently have a macro where I loop through a range of cells to select items in a pivot table. My problem is if I mistype or fat finger an entry in my range, it overwrites whatever pivot item is in the pivot table and gives me mislabled and usually inaccurate data.

My question is, is it possible to tell my macro to return an error/stop running rather than overwrite the pivot item?

I've searched for this extensively and have been unable to find so I may just need to figure something else out, but figured I'd ask myself to see if anybody might have an idea.

I'm using excel 2007. Here is an general example of the current coding:

'VARIABLES AND LABELS

Dim pt As PivotTable
dim report as long
Set pt = Sheets("Pivot Sheet").PivotTables(1)

Dim rangename1 As String
Dim rangename2 As String

'PIVOT TABLE SELECTIONS

Dim queryct As String
Sheets("Range Sheet").Select
queryct = Application.WorksheetFunction.CountA(Range("A:A"))

For report = 2 To queryct Step 1

rangename1 = Sheets("Range Sheet").Cells(report, 4) & ""
rangename2 = Sheets("Range Sheet").Cells(report, 5) & ""

pt.PivotFields("Pivot Item 1").CurrentPage = rangename1
pt.PivotFields("Pivot Item 2").CurrentPage = rangename2

Thanks as always-
Joe
 
Last edited:

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Are you wanting to loop through all items of a PivotTable?
 

Joe_L

New Member
Joined
Mar 10, 2011
Messages
14
Hi Glenn - right now this macro selects whatever pivot item I type into a data range on another tab so it's not usually every pivot item. My problem is if I mistype anything it overwrites the pivot item names when it tries to select. Just wondering if there is a way to prevent this from happening.
 

Joe_L

New Member
Joined
Mar 10, 2011
Messages
14
Ensuring that data validation is not deleted

ok, I gave up on the above idea and just validated each column in the data range using lists. Each column (C to Z) has a different list referenced on another tab. I tried to modify the below seemingly common code within the worksheet to prevent the lists to be overwritten by pasting. Problem now is it seems to work for only 1 column. If I try to apply to the entire range (C2:Z500) I get an "out of stack space" error in Visual Basic and then my workbook crashes. Anybody ever experience this before? Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("C2:D500")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 

Joe_L

New Member
Joined
Mar 10, 2011
Messages
14
I managed to find a solution toying around with the code, and since I've seen this question out there but never found the answer in my searches I'll go ahead and post this up incase anybody like me stumbles on it - and as usual for a novice like me, it's really simple.

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("C2:C500")) And HasValidation(Range("D2:D500")) And HasValidation(Range("E2:E500")) And etc..... Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub
Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 

Watch MrExcel Video

Forum statistics

Threads
1,109,368
Messages
5,528,272
Members
409,813
Latest member
robyrux

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top