Adding same data accross multiple sheets in excel

theterminator

Wise Old Owl
I have an excel which I update daily by creating a sheet for every day and then having a cumulative sheet at the end which adds up all the values in the multiple sheets.

excel sheet.PNG


In the above sheet, I have data for 08.06.2020 & 09.06.2020 and a cumulative sheet that sums up the data in the sheets to the left. I applied the sum function in the Cumulative sheet today and it has cumulative data. The structure of all the sheets are the same so the SUM function worked. But is there a way to automatically add new data of a newly added sheet to the left of Cumulative sheet. For example, I want to feed data of 10.06.2020 and add it to the left of Cumulative but I want that the cumulative sheet automatically updates itself. I've tried by adding a new sheet but the SUM formula won't extend to the new sheet.
 

rhitwick

Democracy is a myth
Use macro.

Add this code to your Cumulative tab

Code:
Private Sub Worksheet_Activate()
Dim wSheet As Worksheet
Dim l As Long

l = 1

    With Me
        .Columns(1).ClearContents
        .Cells(1, 1) = "INDEX"
        .Cells(1, 1).Name = "Index"
    End With
  

    For Each wSheet In Worksheets
        If wSheet.Name <> Me.Name Then
            l = l + 1
                With wSheet
                    .Range("A1").Name = "Start_" & wSheet.Index
                    .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
                    SubAddress:="Index", TextToDisplay:="Back to Index"
                End With

                Me.Hyperlinks.Add Anchor:=Me.Cells(l, 1), Address:="", _
                SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
        End If
    Next wSheet

End Sub

This would create entries of all existing worksheets in the excel with hyperlink to them.
Also, first cell of each worksheet (except cumulative sheet) would have an entry as "back to index" also hyperlink.
Whenever you create a new worksheet, an entry of worksheet name would be added to the cumulative page automatically.

Once you have the Index updated, you can put logic to update rest of the fields in it automatically.
This is how it would look (only INDEX column, rest are added manually)
AutoIndex.PNG
 

rhitwick

Democracy is a myth
For values of rest of the columns, you can use cell ref ( in case values come from same cell of same row in each worksheet)
In that case, you would need to update the ref. everytime (manually)
 
Top Bottom