Countif into VBA from teams excel

Hi guys,

Never used VBA personally, so I am hoping that it works the way I want it to!

I need to do lots of reporting from several teams excel sheets and I have tried to use countifs and they work but only if the sheet is open, otherwise it shows '#VALUE'

Would VBA help solve this? If so I need code to read from various sheets in various cells (each column needs to be linked to a different workbook and possibly different numbered columns).

More than happy to share examples if needed.

Thanks

Weakly.