Excel VBA code to populate summary sheet with formula linking cells

Question

I am attempting to create a summary sheet where there will be 46 rows of data over 7 columns. Thus, I will be pulling the data from 46 sheets and there will be 7 different points of data.

Can I use offset function to simplify this code e.g.

Sub AutoFillSheetNames()
    Dim ActRng As Range
    Dim ActWsName As String
    Dim ActAddress As String
    Dim Ws As Worksheet

    On Error Resume Next

    Set ActRng = Application.ActiveCell
    ActWsName = Application.ActiveSheet.Name
    ActAddress = ActRng.Address(False, False)

    Application.ScreenUpdating = False

    xIndex = 0
    For Each Ws In Application.Worksheets
        If Ws.Name <> ActWsName Then
            ActRng.Offset(xIndex, 0).Value = "='" & Ws.Name & "'!" & ActAddress
            xIndex = xIndex + 1
        End If
    Next

    Application.ScreenUpdating = True
End Sub

I would like to link the actual cells so they can update automatically and VBA is needed because it will be done for many different excel workbooks with different tabnames. The code posted here works when, for example, I link cell F2 on Summary sheet to F2 on the first sheet of 48 sheets and then correctly inputs the formula for the remaining 47 cells down to F48. However, when I want to link cell H2 on summary sheet to G7 on the first sheet, what should I change in the code above?

Side note: I hardly recommend to avoid using On Error Resume Next without using a proper error handling. This way you only mute error messages but the errors are still there and you don't see what's going wrong. — Pᴇʜ
question 1) why do you have 47 sheets? please consider workbook design as this can significantly improve coding. That being said, you can put all of your worksheet names into an array and loop through that array — user1

CMArg CMArg · Accepted Answer · 2017-08-17T16:12:24

I really can't understand what you are trying to accomplish. The code below will link the 7 cells in first row (i.e., A1:G1) of each worksheet in the Summary sheet. There will be as many rows as Sheets. But I don't know if this is what you really want.

Sub AutoFillSheetNames()
    Dim CurrWorkBK As Workbook
    Dim CurrSheet As Worksheet
    Dim TheOtherSheets As Worksheet
    Dim MyRow As Long
    Dim MyCol As Long

    Set CurrWorkBK = ThisWorkbook
    Set CurrSheet = CurrWorkBK.Worksheets("Summary")

    MyRow = 1
    For Each TheOtherSheets In CurrWorkBK.Worksheets
        If TheOtherSheets.Name <> CurrSheet.Name Then
            For MyCol = 1 To 7
                CurrSheet.Cells(MyRow, MyCol).Value = "='" & TheOtherSheets.Name & "'!" & TheOtherSheets.Cells(1, MyCol).Address
            Next MyCol
            MyRow = MyRow + 1
        End If
    Next
End Sub

Excel VBA code to populate summary sheet with formula linking cells

1 Answers