How to select same Range in multiple sheets in VBA?

Question

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...

You mean it is good?? But here I need to write code 5 times.. so tring these way — Pink Pink
– Pink Pink, Commented Sep 14, 2016 at 11:56

Shai Rado · Accepted Answer · 2016-09-14 12:12:50Z

1

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

answered Sep 14, 2016 at 12:12

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

1 Comment

Shai Rado Over a year ago

@SwethaReddy you're welcome, thanks for accepting my answer

Gary's Student · Accepted Answer · 2016-09-14 12:11:12Z

1

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)

answered Sep 14, 2016 at 12:11

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

How to select same Range in multiple sheets in VBA?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related