Generate reports in batches of 40 and export to PDF

[Waimea]

Hi,

I have an outer FOR loop and an inner FOR loop and at the end of the inner FOR loop I am trying to check if a worksheet exists, if it doesn't create it and then dump the value of my array into the new worksheet.

At the moment the code keeps generating new spreadsheets and I am not sure why it does.

Option Explicit


Sub OuterAndInnerLoop()


Dim L As Long
Dim N As Long
Dim R As Long
Dim WkArr
Dim i As Long


Dim sht As Worksheet
Set sht = Sheets("Dash")




ReDim WkArr(1 To 40 * 40, 1 To 4)


ThisWorkbook.Worksheets("Dash").ComboBox1.ListIndex = 1



' Outer loop
For N = 0 To 39


Sheets("Dash").ComboBox3.ListIndex = N


' Inner loop
For L = 0 To 39
     
    With Sheets("Dash")    
        .ComboBox2.ListIndex = 1


        .ComboBox4.ListIndex = L
    End With

  
   i = i + 1
       
           
    Sheets("Dash").ComboBox4.ListIndex = L
    
    R = Cells(Rows.Count, 1).End(xlUp).Row + 1

    State1 = Sheets("Dash").ComboBox1.Value
    City1 = Sheets("Dash").ComboBox3.Value


    State2 = Sheets("Dash").ComboBox2.Value
    City2 = Sheets("Dash").ComboBox4.Value



        
    WkArr(i, 1) = State1
    WkArr(i, 2) = City1
    
    WkArr(i, 3) = State2
    WkArr(i, 4) = City2
  
 
    
    'Test report
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                
    Select Case L
'
    Case Is = 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38
        


    Case Is = 39
    Worksheets.Add.Name = City1
    
    With Sheets("City1")
        .Cells(R, 1).Resize(UBound(WkArr, 1), UBound(WkArr, 2)) = WkArr
    End With
      
    Case Else
    MsgBox "Debug"
    
    
    End Select

Expected output in the new spreadsheet is:

State1 City1 State2 City1
State1 City1 State2 City2
State1 City1 State2 City3
...
State1 City40 State2 City 40

Content of ComboBox3 and ComboBox4 is:

City1
City2
City3
...
City39
City40

This code doesn't dump the entire value of my array into the correct spreadsheet.

I would like help with how to:

1. Check if the worksheet exists, if not create it.

2. If it exists dump the value of the array for the inner loop into the new worksheet, with values of all combinations of City1 with City1-City40.

As it is now my code keeps generating new spreadsheets when I only want it to create a spreadsheet called City1 and in that spreadsheet have the entire value of my array and keep doing this for the entire array.

 

[Waimea]

It turns out that it works if I place my code outside of the inner loop.

Now I get the values of my Array in a new worksheet.

Would it be possible to dynamically offset the results of my array?

So that City1 vs City1 would be in Cells(1,1) and City1 vs City2 would be in Cells(5,1)?

 

[Waimea]

I am still trying to create new worksheets in this double loop code, I am looking at the locals window but I can't figure out where to place the code to add a new worksheet. No matter where I place the code I get additional worksheets created that are named different from my naming variable.

For each loop 0 to 39 I want to generate 1 worksheet named City1 which contains all combinations of City1 vs. City2, which gives me 40 combinations for each loop.

I have been thinking about redimension the array or slice the 40 first items of the array, slice the next 40 items and so forth until the end of the loop.