VBAに頼り始めると、まずは関数を使って結果を出す。という意識が薄れがち
Excel.vba
dim sh
sh = sheets(1).name
とかで取得しようとする、僕の悪い癖（杉下風）
関数でどうやるかというと、Cell関数を使う
■Cell関数
検査値として "filename"を指定してあげればファイル名が取得されるので
Excel.関数
=cell("filename",A1)
としておくと,A1セルにフルパス名\[ファイル名]シート名で出力される
で、ほしいのはシート名なので、"]"より右側だけほしい
Excel.関数
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
↑
この部分はもう少しスマートにならないかなとも思うけど、こんな感じ