CODEWITHSUNDEEP
excelvba
Pink Pink
Pink Pink

Reputation: 39

How to select same Range in multiple sheets in VBA?

I am having 4 Sheets, say...,

 STP1
 STP2
 STP3
 STP4
 STP5

I want to update all D3 cells of all the 5 sheets at same time.

I am trying in this way..,

  Worksheets(Array("STP1", "STP2", "STP3", "STP4", "STP5")).Select
  Worksheets(Array("STP1", "STP2", "STP3", "STP4",   "STP5")).Range("D3").Select
  If Worksheets(Array("STP1", "STP2", "STP3", "STP4", "STP5").Range("D3").Offset(0, 0) = "" Then
  Worksheets(Array("STP1", "STP2", "STP3", "STP4", "STP5")).Range("D3").Select.End(xlDown).Select
  End If
  ActiveCell.Offset(0, 0).Select
  ActiveCell.Value = Eng

But this is not working out... Can anyone please help me here...

Upvotes: 1

Views: 3153

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

Is this code too long (looping through all Sheets), using a Select Case where you can add as many sheets as you like.

(It is better to avoid selecting cells)

Option Explicit

Sub SelectSheets()

Dim Sht             As Worksheet

For Each Sht In ThisWorkbook.Sheets
    Select Case Sht.Name
        Case "STP1", "STP2", "STP3", "STP4", "STP5"

            If Sht.Range("D3") = "" Then
                Sht.Range("D" & Sht.Cells(Sht.Rows.Count, "D").End(xlUp).Row).Value = Eng
            Else
                Sht.Range("D3").Value = Eng
            End If

    End Select

Next Sht

End Sub

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

Consider:

Sub Macro1()
    Sheets(Array("STP1", "STP2", "STP3", "STP4", "STP5")).Select
    Sheets("STP1").Activate
    Range("D3").Select
    ActiveCell.Value = "whatever"
End Sub

(but I would use a loop and no Selects)

Upvotes: 1

Related Questions