To make sure I understand, let me rephrase: you have a workbook with a
number of tabs in it, labeled A, B, C etc. A!G1 contains the value 1.
B!G1 contains the formula =A!G1+1 which you want to copy into the same
cell on C, D, and E. The formula should pick up the value from
previous tab and increment it by 1 each time, so that Z!G1 should show
26. Do I have that right?

I'm not aware of an automated function within Excel to do this. It can
be accomplished with a bit of code that for each tab reads the current
tab name, converts it to an ascii value, augments that value by one,
with exceptions for flipping from Z back to A and with accommodations
for tab names that are longer than 1 character, then using the new
ascii value to generate a formula in G1.