Skipping an i, or multiple i's in a Loop - please help

[mtdewrocks]

I am writing some code to generate multiple pivot tables in excel. My spreadsheet is set up with survey responses to a variety of questions with the question number across the top, and the survey responses below in the column. My problem relates to questions 8, 10, and 13. As you can see in my sample table, most questions have only one possible response so the code is pretty easy. I can also write the code to generate a pivot table for all of question 8's responses, but am trying to figure out what would be the best way to handle this situation.

I want it to generate a pivot table for questions 1 - 7, then generate one pivot table based on all question 8 responses, and then skip over i's to get to question 9. In this example I want it to get to I=8, and then skip to I=11 so it does not try to generate the pivot tables for questions 8_2, 8_3. I will also need to do this again for my question 10 and 13, but if someone can get me the coding for this one I assume it would be the same process, just different I's. My code is posted below.

Q_1 Q_2 Q_3 Q_4 Q_5......Q_8_1 Q_8_2 Q_8_3
2 1 2 4 3
3 7 5 1 2

Sub MakePivotTables()
'   This procedure creates multiple pivot tables
    Dim PTCache As PivotCache
    Dim pt As Pivottable
    Dim SummarySheet As Worksheet
    Dim ItemName As String
    Dim Row As Long, Col As Long, i As Long
    Dim Period As String
    Dim QBPRCOML As String
    
    Application.ScreenUpdating = False
    
'   Delete Summary sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Summary").Delete
    On Error GoTo 0
    
'   Add Summary sheet
    Set SummarySheet = Worksheets.Add
    ActiveSheet.Name = "Summary"
    
'   Create Pivot Cache
    Set PTCache = ActiveWorkbook.PivotCaches.Create( _
      SourceType:=xlDatabase, _
      SourceData:=Sheets("MASTER").Range("A1"). _
        CurrentRegion)
    
    Row = 3
    For i = 1 To 20
      For Col = 1 To 14 Step 13 '2 columns
        ItemName = Sheets("MASTER").Cells(1, i + 19)
        With Cells(Row, Col)
            .Value = ItemName
            .Font.Size = 16
        End With
    
    Period = "20160331"
     QBPRCOML = "KC"
     
'       Create pivot table
        Set pt = ActiveSheet.PivotTables.Add( _
          PivotCache:=PTCache, _
          TableDestination:=SummarySheet.Cells(Row + 1, Col))
        
'       Add the fields
        If Col = 1 Then 'Frequency tables
            With pt.PivotFields("CERT")
              .Orientation = xlDataField
              .Name = "Frequency"
              .Function = xlCount
            End With
        Else ' Percent tables
        With pt.PivotFields("CERT")
            .Orientation = xlDataField
            .Name = "Percent"
            .Function = xlCount
            .Calculation = xlPercentOfColumn
            .NumberFormat = "0.0%"
        End With
        End If
        
        pt.PivotFields("CALLYMD").Orientation = xlPageField
        pt.PivotFields("CALLYMD").Position = 1
      
        pt.PivotFields("REGION").Orientation = xlPageField
        pt.PivotFields("REGION").Position = 1
        pt.PivotFields("REGION").CurrentPage = QBPRCOML

    With pt.PivotFields(ItemName)
        .Orientation = xlRowField
        .ShowAllItems = True
    End With
        pt.DisplayFieldCaptions = False
      Next Col
        Row = Row + 15
   Next i
   
End Sub

 

[mtdewrocks]

I am open to other ideas on how to approach this problem as well as I am somewhat new to VBA. Thanks!

 

[mtdewrocks]

Bump

 

[_squirrel]

Can you give us an example to reproduce your problem? With the SHORTEST POSSIBLE code and data?

 

[mtdewrocks]

Can you give us an example to reproduce your problem? With the SHORTEST POSSIBLE code and data?

Sub MakePivotTables()
' This procedure creates multiple pivot tables
Dim PTCache As PivotCache
Dim pt As Pivottable
Dim SummarySheet As Worksheet
Dim ItemName As String
Dim Row As Long, Col As Long, i As Long
Dim Period As String
Dim QBPRCOML As String

Application.ScreenUpdating = False

' Delete Summary sheet if it exists
On Error Resume Next
Application.DisplayAlerts = False
Sheets("Summary").Delete
On Error GoTo 0

' Add Summary sheet
Set SummarySheet = Worksheets.Add
ActiveSheet.Name = "Summary"

' Create Pivot Cache
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Sheets("MASTER").Range("A1"). _
CurrentRegion)

Row = 3
For i = 1 To 20
ItemName = Sheets("MASTER").Cells(1, i + 19)
With Cells(Row, Col)
.Value = ItemName
.Font.Size = 16
End With


' Create pivot table
Set pt = ActiveSheet.PivotTables.Add( _
PivotCache:=PTCache, _
TableDestination:=SummarySheet.Cells(Row + 1, Col))

' Add the fields
With pt.PivotFields("CERT")
.Orientation = xlDataField
.Name = "Frequency"
.Function = xlCount
End With

pt.PivotFields("CALLYMD").Orientation = xlPageField
pt.PivotFields("CALLYMD").Position = 1

pt.PivotFields("REGION").Orientation = xlPageField
pt.PivotFields("REGION").Position = 1
pt.PivotFields("REGION").CurrentPage = QBPRCOML

With pt.PivotFields(ItemName)
.Orientation = xlRowField
.ShowAllItems = True
End With
pt.DisplayFieldCaptions = False
Row = Row + 15
Next i

End Sub

Try that - I am new to VBA programming so if you have ideas on how to shorten it, I am open. I basically need a bunch of pivot tables, but for some questions there are multiple parts - i.e. 8_1, 8_2, 8_3 - should all go into one pivot table. I think I can write that code okay - what I want to do though is skip the "I" that would equate to 8_2, 8_3 so I don't get worthless pivot tables, but I need it to also continue on to question 9, 10, and so forth. My approach now which isn't the best is to just let the code generate worthless tables and then just tell it to delete those rows including the pivot tables after that code runs.

 

[_squirrel]

The first thing that comes to mind is to add something like this just after you start your loop:

[COLOR=#333333]For i = 1 To 20
    If i = 8 Or i = 10 Or i = 13 Then
        i = i + 1
    End If
    [/COLOR][COLOR=#333333]ItemName = Sheets("MASTER").Cells(1, i + 19)[/COLOR][COLOR=#333333]
[/COLOR]

You could then write a new procedure to handle cases 8, 10, 13.

Does that solve your problem?

 

[mtdewrocks]

I am writing new procedures to handle cases 8, 10, and 13, but that won't quite work exactly - for example - in question 8 there are 13 option choices, and 10 there are 4, and 13 there are about 12 choices.

However, I think I can use what you gave me and tweak it slightly to work perfectly.

I really appreciate your help. Also, I was writing new sub procedures to create pivot tables for questions 8, 10, and 13 and then calling the procedures. Is calling other procedures the best way to handle it, or how would you recommend I run the pivot tables for these questions if that makes sense?