I tested the following which seems to work and could replace Golemic's code immediately above. Since I deal with dozens of currencies, a SELECT CASE construct is not so helpful for me.

WantedCurrencyCode as String
WantedCurrencyCode = "#,###,##0.00 [$" + Range("A1").Value + "]" Worksheets("Sheet2").Range("Table1").NumberFormat = WantedCurrencyCode Worksheets("Sheet3").Range("Table2").NumberFormat = WantedCurrencyCode Worksheets("Sheet4").Range("Table3").NumberFormat = WantedCurrencyCode