For loop preserving previous iteration

Question

I don't have much experience with VBA so it's been difficult to troubleshoot this. When running the code, it outputs Array(i<=i) instead of Array(i)

I've tested the for condition and found Array(0) properly returns the result. However Array(1) will print Array(1) with Array(0) and so on.

The goal of this code is to group worksheets based on their name and print them to pdfs based on grouping, i.e. all sheets starting with I1 to a single pdf.

Sub Test()
    FolderPath = "C:\Example"

    Dim aWS()
    Dim n As Integer
    Dim ws As Worksheet
    Dim DocTypes()

    DocTypes = Array("I1","I2","I3")

    For i = LBound(DocTypes) To UBound(DocTypes)
        For Each ws In Worksheets
            If Left(ws.Name, 2) = DocTypes(i) Then
                n = n + 1
                ReDim Preserve aWS(1 To n) 'Add 1 to to array length
                aWS(n) = ws.Name 'Add worksheet name meeting If condition 
            End If
        Next ws  

        Sheets(aWS).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & 
            DocTypes(i), _
            openafterpublish:=True, ignoreprintareas:=False
    Next i
End Sub

What I expect is:

i = 0 to 2

First Array(i) = "I1" so output all sheets beginning with "I1" as a pdf

Then move to i = 1

Here Array(i) = "I2" so output all sheets beginning with "I2" as a pdf

However when I step forward it doesn't seem to be following this logic and I don't understand why. I'm thinking it has to do with the selection, it would follow that if i=0 was selected, then i=1 was added to the selection this problem would make sense. I've tried re-selecting a single sheet right before Next i to force past this but it didn't work. This leads me to think I've made a logical mistake in my for loops.

Answer 1

Use Selection.ExportAsFixedFormat etc instead of ActiveSheet. The ActiveSheet is always only one sheet while your selection comprises many. Upon further study I find that you may have to include making a selection for each of the worksheets, like Ws.UsedRange.Select. Take a look at this thread.

Answer 2

You might not be aware but you can use a variant as a control variable in a for each to iterate an array of variants. Your use of redim to extend an array by 1 item suggests that you should be using a scripting dictionary as an intermediate step to your array. The .Items method of a scripting dictionary returns an array of items so it is easy to get your array that you use subsequently. Here is your code revised to use a scripting.dictionary and a variant control variable. In your specific case we are basically using the scripting.dictionary as a list by making the key and the item the same thing.

Option Explicit

Sub Test()

    Const FolderPath                        As String = "C:\Example"

    Dim aWS                                 As Scripting.Dictionary
    Dim ws                                  As excel.Worksheet
    Dim DocTypes()                          As Variant
    Dim DocType                             As Variant

    DocTypes = Array("I1", "I2", "I3")

    For Each DocType In DocTypes

        Set aWS = New Scripting.Dictionary

        For Each ws In Worksheets

            If DocType = left(ws.Name, 2) Then

                aWS.Add Key:=ws.Name, Item:=ws.Name

            End If

        Next ws

        Sheets(aWS.Items).Select
        ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            FileName:=FolderPath & DocType, _
            openafterpublish:=True, _
            ignoreprintareas:=False
    Next

End Sub