2 choices. One is to do all the copying and then do a Find/Replace to
change the sheet name. This would be tedious.

Two is to put the sheet names into a cell somewhere, probably near the
top of the column. You can reference that cell using INDIRECT so your
formula is the same all the way across. If the sheet names are in the
2nd row:
=INDIRECT(A$2&"$D$2")