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? Am i assigning the cells
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 'issue is here data_wb.Sheets(2).Range(("F" & CStr(Count))) = sheet.Range("w26").Value 'issue is here Next k Next j Next i