As far as using a Named Range, you can create a dynamic named range to refer to all sheets via a couple of different methods.
This first method will create a Named Range across all sheets. StartSheet and EndSheet are the actual names of the first and last sheet in your workbook. As long as the name of the first and last sheet don't change, it will always include all sheets in between. In other words, as long as your new sheets are inserted between the first and last sheet, the named range will "update" to include the new sheet.
If you can't control where the new sheets will be added, or if they will always be added after the last sheet, then try this event triggered code:
First, create a Named Range using the method above so that it Refers to the first and last sheet and the desired cells. I used AllSheets as the Name.
Place this code in the ThisWorkbook module and it will fire whenever you add a new sheet, regardless of where you add it. It will reset the Named Range to include all sheets.
Make sure you update the R1C1 reference to reflect your desired range.
Note: If you move the last sheet from the last position to any other position, the Named Range will not change and will then refer to the first sheet through the new position of the sheet you moved. If you need to be able to move sheets arounds, specifically out of the last position, then we'll need a method that fires the code based on some other event so that the name always gets updated with the name of the last sheet.
Let me know if this works for you.
Private Sub Workbook_NewSheet(ByVal Sh As Object)
'Note: The current R1C1 reference Refers To A1:C5
'You'll need to change that to match your range
.RefersToR1C1 = "=Sheet1:" & Sheets(Sheets.Count).Name & "!R1C1:R5C3"
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.