CODEWITHSUNDEEP
vbaexcel
Topher
Topher

Reputation: 5

Reference Another Sheet Using a Variable for the Sheet Name

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

Upvotes: 0

Views: 1177

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

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.

Upvotes: 6

Related Questions