Copy paste without incrementing - can't use absolute references

Hi,
I have a question on the spreadsheet that is included. (it is normal if you see lots of #REF in cells, I have deleted a lot of tabs and changed names to make this spreadsheet generic)
The “planifdata” tab is a table I am building in order to get pivot table data for project reporting. There are 25 tabs in the regular spreadsheet.

I want to be able to copy-paste the range D4 to AK108 25 times one after the other. The only thing I want to change is the reference to the tab in the formulas. (I could do a find and replace page-1 once everything is copied).

I am unable to copy without excel incrementing the cells. Since the formulas are not all the same in that range, I can’t put in absolute references with the $ sign.

Hi, Thanks for your answer.
I have been playing with the indirect function following your suggestion but I can't seem to make it work. Excel keeps returning a formula error.
What would the syntax be for this function for instance
IFERROR('Page-1'!AG5*'Page-1'!$AC5,"")
I want this formula to stay the same when I copy paste it so I can then replace page-1 by page-2 through a find and replace.

Thank you

hi,

This actually does solve my problem but just for one cell. I am attaching your spreadsheet with added information. In the two tabs Page 1 and page 2, I have added data in colums B and C.
I want the formula to allow me to increment when I drag across colums B and C. I am really sorry, I just realized I had not specified that initially. Apologies. You have already helped me immensely

Indirect help.xlsx

With the attachement

In fact, here is the actual formula

INDIRECT("'" & $D7 & "'" & "!ag$5")*INDIRECT("'" & $D7 & "'" & "!Ac5")

AG5 has to increment to AH5 towards the right and AG6 down
AC5 should stay the same horizontally, but increment to AC6 vertically

Hi Again! I found it. I've fixed the problem.
In case someone is searching one day, here is the formula with incrementing across and vertical
IFERROR(INDIRECT("'" & $D4 & "'" & "!"&CELL("address",AG5))*INDIRECT("'" & $D4 & "'" & "!"&CELL("address",$AC5)),"")