Replace Fill Down, Complicated Equation

Another option. You haven't stated what you want to trigger the macro running so I haven't put any autorun in the program.

Dim Sh As Worksheet
    Dim LR As Long
    For Each Sh In Worksheets(Array("Sheet2", "Sheet4", "Sheet9"))
        LR = Sh.Range("B" & Rows.Count).End(xlUp).Row
        Sh.Range("H1").Formula = _
        "=HYPERLINK(CONCATENATE(""file:///\\uranus\dckgen\Brands\Zoom\Brand - Zoom\Upload Photos\2012\week"",B1,""\BHS_WK"",B1,""_LR\"",E1,"".jpg""))"
        Sh.Range("H1").AutoFill Destination:=Sh.Range("H1:H" & LR), Type:=xlFillDefault
Next Sh
End Sub

I altered it to cover the exact sheets named, and I get a compile error.

I changed the line

For Each Sh In Worksheets(Array("Sheet2", "Sheet4", "Sheet9"))

to

For Each Sh In Worksheets(Array("BHSDB", "BurtonDB", "DaisyAndEveDB", "DivaDB", "DorothyPerkinsDB", "FreedomDB", "GinaTricotDB", "MatalanDB", "TopMan", "WallisDB"))