Excel - import data from cell automatically when a new tab is created

Question

I am working on a spreadsheet summarizing data from standard templates inserted in a joint Excel file as separate tabs.

Is it possible to automatically import data from a specified cell into a summary tab, when a new tab is created? Since the data I want to extract is in the same cell in each document, this would save me a lot of time and effort

Answer 1

Something like this will create a list in sheet1 column A of Sheet Names, and every time cell C1 on any sheet changes, it will place the value of that cell into sheet1 in column B, next to the corresponding sheet name in column A

It goes in the ThisWorkbook module through the VBA editor*. You can change the sheet name and ranges as needed.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Not Intersect(Target, Sh.Range("C1")) Is Nothing Then
        Dim ws As Worksheet
        Set ws = Sheets("Sheet1")
        Dim iRow As Integer
        On Error GoTo Yikes:
TryAgain:
        iRow = Application.WorksheetFunction.Match(Sh.Name, ws.Range("A1:A50"), 0)
        ws.Cells(iRow, 2) = Target.Value
        Exit Sub

Yikes:

        Dim rowcount As Integer
        rowcount = ws.Cells(Rows.Count, 1).End(xlUp).Row
        Dim r As Range
        Set r = ws.Cells(rowcount + 1, 1)
        r = Sh.Name
        GoTo TryAgain:
    End If
End Sub

*To get to the VBA editor, you must go to the office button, excel options and in the popular tag, place a checkmark in the box next to Show Developer tab in the Ribbon. Then close that out, go to the developer tab and click the Visual Basic button on the far left. The editor will open up and you will need to double click the ThisWorkbook module on the left pane.