Na sua linha .LeftHeader = ...
você basicamente forneceu ao Excel uma string.
No VBA, uma string deve ser citada com "
, a menos que seja uma constante VB interna (por exemplo, vbCr
) ou uma variável (por exemplo, Range("J2").Text
).
Portanto, tente esta linha:
.LeftHeader = vbCr & vbCr & vbCr & "&B &9" & Range("J2").Text & vbCr & vbCr & Range("J3").Text & vbCr & Range("J4").Text & vbCr & Range("J5")
Isso passará ao Excel uma string que contém as entradas, os valores dos intervalos e o texto "& B & 9" que o Excel analisará como Negrito e outra coisa (não sabe o que o & 9 representa para)