Hi p45cal, sorry bout the confusion on the comments. Most of them are useless, just stuff I experimented with but left it there in case I want to get back to it. The line that should be running is this
Code:
VoltageDrop = WorksheetFunction.VLookup(Val(LoadCurrentText.Text), Worksheets(WorksheetName).Range(Cells(10, Column), Cells(26, VoltageDropColumn)), ReturnColumn)
which is uncommented at line 365.
I run this line several times throughout the codsso once I've figured out one of them, the rest should be fine as well.

As for the Microsoft Office Web Components, that's the spreadsheet in the userform which is currently working fine. So if you don't mind, you could delete that spreadsheet and in the codes that start with DataTable. lines:10 to 18, and the entire private_sub tables_change().

I have a total of 8 worksheets, from DataPVC1 to DataPVC4 and DataThermo1 to DataThermo4. I've deleted 6 of these worksheets as the file was too big to upload. I just realized I used the string format for the WorksheetNumber!! No wonder it only works for worksheets labelled 1. Thanks for pointing this out! I'll go try out again before updating here.