Reading in cells linked to multiple list validations

[seanbarker]

I am trying to update 3 different list validation cells, running through all possible combinations of the 3, and then read in some cell values.

The loop seems to work fine, and changes the list values correctly, but the final assignment of the cell values to my other workbook doesnt seem to work. It takes the values in the cells when the sheet is first open, but they dont update based on each new entry in the list validation cells.

Can anyone tell me where im going wrong?

Below is the relevent code where i am doing the looping

For i = LBound(countries) To UBound(countries) - 1
    'close and repopen workbook as seems to be causing issues
    'emissions_wb.Close
    'Set emissions_wb = Workbooks.Open(filepath & filename)
    'Set emissions_wb = Workbooks(filename)
    'Set sheet = emissions_wb.Sheets(2)
    countries_drop_down_cell = countries(i)
    'create unique airport list for specific country
    airports = airports_by_country(i)
    
    Application.Calculate
    'if statement as last entry is just one airport so not a variant hence cant use UBound method
    
    For j = LBound(airports) To UBound(airports)
    
        airports_drop_down_cell = airports(j, 1)
        Application.Calculate
        For k = LBound(years) To UBound(years)
            Count = Count + 1
            years_drop_down_cell = years(k)
            
            'recalculate sheet
            Application.Calculate
            
            'store variables in another workbook
            data_wb.Sheets(2).Range(("D" & CStr(Count))) = years(k)
            data_wb.Sheets(2).Range(("C" & CStr(Count))) = airports(j, 1)
            data_wb.Sheets(2).Range(("B" & CStr(Count))) = countries(i)
            data_wb.Sheets(2).Range(("E" & CStr(Count))) = sheet.Range("w24").Value
            data_wb.Sheets(2).Range(("F" & CStr(Count))) = sheet.Range("w26").Value
        Next k
    Next j
Next i