Reference Another Sheet Using a Variable for the Sheet Name

Question

I would like to reference each sheet in this formula by specifying the sheet name with the variable, "sheetname". Does anyone know how to do this?

Sub PopulateRow()

Dim WS_Count As Integer
Dim I As Integer
Dim sheetname As String

WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

    Worksheets(I).Activate
        sheetname = ActiveSheet.Name
    Worksheets(1).Cells(I, 1).Formula = "=sum('sheetname'!d:d)"

Next I

End Sub

Scott Craner · Accepted Answer · 2016-07-28 17:33:32Z

6

You would pull the variable outside the quotes and concatenate with &.

Sub PopulateRow()

Dim WS_Count As Integer
Dim I As Integer


WS_Count = ActiveWorkbook.Worksheets.Count

For I = 1 To WS_Count

    Worksheets(1).Cells(I, 1).Formula = "=sum('" & Worksheets(I).Name & "'!d:d)"

Next I

End Sub

As a note I removed the sheet.Activate. It will slow down the code and is not needed if done properly.

answered Jul 28, 2016 at 17:33

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Scott Craner Over a year ago

@Topher please mark as correct by clicking the check mark by the answer. It is something only you can do.

Collectives™ on Stack Overflow

Reference Another Sheet Using a Variable for the Sheet Name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related