Auto Naming Excel Sheets and Function Across Sheets

Here's the scenario I faced. I was given an Excel file which

• consists of 120 sheets of data in uniform template, that
• any of them belong to only one of 3 main categories, and
• under each category, any of them belongs to only one of 3 regions, and
• I have to summarise them in a single "Summary" sheet.

Here what I've done. Because the Excel sheets are named somewhat arbitrary, I firstly need to rename them to a more consistent grouping.

Public Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range) Dim nameln As Integer Dim shname As String . nameln = Application.WorksheetFunction.Find(" ", Range("A1"), 13) shname = Left(Range("A1"), nameln - 1) ActiveSheet.Name = shname & " " & shname2 ActiveSheet.Next.Select Application.Wait (Now + TimeValue("0:00:01")) Range("I1").Value = "" End Sub

After that, it is easier for me to use the SUM function across multiple sheets.=sum('Sales Oil Reg1 Cntrct1:Sales Oil Reg3 Cntrct120'!F20)

Cell F20 in each sheet represents the total sales for each Contract, so in the above formula, it summarises all the sales from all contract from all region.

Here is another useful trick- because I made the auto naming to include its main category and region, I can do the function for only the sheets under each category (or region) by the following=sum('Sales Oil*'!F20)

By the way, once you press Enter after typing in the formula, what Excel did was it analyse the shorthand formula and rewrite them if you were to do manually like =sum('Sales Oil Reg1 Cntrct1'!F20, 'Sales Oil Reg1 Cntrct2'!F20) and so on.