Specifying a range across all sheets in the workbook

[pells]

Is it possible to specify a range across all worksheets in a workbook?

I am looking to check across all workbooks in cell range R6 to see if anything is entered in the cell. ActiveSheet.Range ("R6") works for the active sheet but I need to check across all sheets.

Many thanks.

 

[njimack]

Something like this:

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
Next ws
End Sub

 

[Michael M]

Maybe something like

Dim was As Worksheet
For Each ws In Worksheets
    If Range("A6").Value = "" Then
    ....more code here
Next ws

 

[pells]

Something like this:

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
Next ws
End Sub

Many thanks for this njimack.

I think I made a slight mistake with my original post as I do need to specify some worksheet names but not all of them - I hope this makes sense?

This can be done?

Once again, many thanks for your reply.

 

[njimack]

Amended for specific sheet names

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name = "Sheet1" or ws.Name = "Sheet2" Then 'amend as required
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
End If
Next ws
End Sub

 

[pells]

Amended for specific sheet names

Sub Test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name = "Sheet1" or ws.Name = "Sheet2" Then 'amend as required
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
End If
Next ws
End Sub

Fantasic - many thanks for this, it works perfectly!

 

[njimack]

Glad it worked, thanks for the feedback.

 

[pgc01]

Hi

Another option:

Sub Test()
Dim ws As Worksheet
 
For Each ws In ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2"))
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
Next ws
End Sub

 

[pells]

Hi

Another option:

Sub Test()
Dim ws As Worksheet
 
For Each ws In ThisWorkbook.Worksheets(Array("Sheet1", "Sheet2"))
    If ws.Range("R6") <> "" Then MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
Next ws
End Sub

Many thanks pgc01, this works perfectly too, brilliant!

I am just thinking, how do I go about making a slight change to this so that if cell R6 is blank across all sheets that I specify, then continue to run the sub and if cell R6 is not blank, stop the sub?

Once again, many thanks.

 

[njimack]

Many thanks pgc01, this works perfectly too, brilliant!

I am just thinking, how do I go about making a slight change to this so that if cell R6 is blank across all sheets that I specify, then continue to run the sub and if cell R6 is not blank, stop the sub?

Once again, many thanks.

This should do the trick (untested)

Sub Test()
Dim ws As Worksheet
Dim Count As Integer
For Each ws In ThisWorkbook.Sheets
If ws.Name = "Sheet1" Or ws.Name = "Sheet2" Then 'amend as required
    If ws.Range("R6") <> "" Then
        Count = Count + 1
        MsgBox "Sheet name:" & ws.Name & vbCr & "Value in R6:" & ws.Range("R6").Value
    End If
End If
Next ws
If Count = 0 Then Exit Sub
'rest of your code here
End Sub