Solved: Referencing Another Sheet in Workbook

[Opv]

I have a script in a Module which performs a function, for example, in Sheet(2) but needs to reference a range within Sheet(1). I am being forced to "Activate" Sheet 1 to avoid receiving an application error when the script attempts to accommodate the referenced range on that Sheet.

Example:
[VBA]
c = Sheets(1).Range("A4", Range("A4").End(xlDown)).Select
[/VBA]

If I am in Sheet2 when this script is run, it receives an error, but it works find if Sheet1 is the active sheet.

Could someone please help me understand the significance and benefit of including "Sheets(1)" in the script if the sheet has to be activated in order for the script to run properly?

Is there a way around this without having to include a "Sheets(1).Activate" statement?

[mdmackillop]

You need to reference both elements within the range description
[VBA]Dim c As Range
With Sheets(1)
Set c = Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
[/VBA]

[Opv]

You need to reference both elements within the range description
[vba]Dim c As Range
With Sheets(1)
Set c = Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With
[/vba]

Thanks. I'm still struggling to get this to work. It's late and my brain has about shut down for the day. I'll work with it some more tomorrow (hopefully with a fresher brain) and post a sample sheet.

Thanks again,

Opv

[mdmackillop]

It's a shortcut for
[vba]Set c = Range(Sheets(1).Cells(4, 1), Sheets(1).Cells(4, 1).End(xlDown)) [/vba]
As soon as you deal with two sheets, the unreferenced cell defaults to the active sheet.

It would be like
[vba]
Set c = Range(Sheets(1).Cells(4, 1), Sheets(6).Cells(4, 1).End(xlDown))
[/vba]

[Opv]

It's a shortcut for
[vba]Set c = Range(Sheets(1).Cells(4, 1), Sheets(1).Cells(4, 1).End(xlDown)) [/vba] As soon as you deal with two sheets, the unreferenced cell defaults to the active sheet.

It would be like
[vba]
Set c = Range(Sheets(1).Cells(4, 1), Sheets(6).Cells(4, 1).End(xlDown))
[/vba]

Thanks. I understand the implications of what you are saying. I seem to be able to obtain values from (or populate cells in) other sheets without first having to activate the sheets. No problem there. Where I'm running into a problem is when I want to do something like, for example, count visible rows in another sheet.

I'll post a sample sheet with examples of the types of things I'm talking about.

Thanks again,

Opv

[Opv]

Here is an example of what I'm talking about. The script works if Sheet1 is active but I receive an error if Sheet2 is active. Since I will be working in Sheet2, I'm wanting it the script to go do it's thing without having to activate Sheet1, if possible.

Thanks,

Opv

[austenr]

With sheet 2 activated, this worked for me

[vba]Sub countItems()
Dim visItems As Integer

Sheets(1).Activate
visItems = 0
Application.ScreenUpdating = False
With Sheets(1)
.Range("A1").Select
Do Until ActiveCell.Value = ""
If Selection.EntireRow.Hidden = False Then
visItems = visItems + 1
End If
ActiveCell.Offset(1, 0).Select
Loop
End With
Sheets(2).Range("E3").Value = visItems
Sheets(2).Activate
Application.ScreenUpdating = True
End Sub
[/vba]

I moved the

.Range("A1").Select inside the With block. Your original code had it outside the With block. Also added
[VBA]Sheets(2).Activate[/VBA]
so when you are on sheet 2 and run the macro, sheet2 remains the active sheet after macro completion. Take it out if you don't want that to be the case.

[mdmackillop]

Try not to activate or select anything, then you don't need to worry about screen updating if you only return a single value
Always use Long, rather than integer. There is no benefit to integer and it is too "small" to hold all possible returned values.

[vba]
Option Explicit

Sub countItemsLooping()
Dim visItems As Long
Dim Rw As Long

visItems = 0
Rw = 1

With Sheets(1)
Do Until .Cells(Rw, 1).Value = ""
If .Cells(Rw, 1).EntireRow.Hidden = False Then
visItems = visItems + 1
End If
Rw = Rw + 1
Loop
End With
Sheets(2).Range("E3").Value = visItems
End Sub

Sub countItemsNoLooping()
Dim Rng As Range

With Sheets(1)
Set Rng = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
Set Rng = Rng.SpecialCells(xlCellTypeVisible) 'Could be appended in one line above

Sheets(2).Range("E3").Value = Rng.Cells.Count
End Sub

[/vba]

[GTO]

Hi Opv,

Afraid I wrote but had to leave before posting. In total agreeance with Malcom's comments, here's two more examples, though "..._3" is nearly identical to Malcom's first.

Sub CountItems_3()
Dim lVisItems As Long
Dim rngSearch As Range
Dim i As Long
    
    With ThisWorkbook.Worksheets(1)
        Set rngSearch = .Range("A:A")
        i = 1
        Do While Not rngSearch(i).Value = vbNullString
            If Not rngSearch(i).EntireRow.Hidden Then
                lVisItems = lVisItems + 1
            End If
            i = i + 1
        Loop
    End With
    
    ThisWorkbook.Worksheets(2).Range("E3").Value = lVisItems
End Sub
    
Sub CountItems_2()
Dim lVisItems As Long
Dim rngSearch As Range
Dim rngCell As Range
    With ThisWorkbook.Worksheets(1)
        Set rngSearch = Range(.Range("A1"), .Range("A1").End(xlDown))
        
        For Each rngCell In rngSearch
            If Not rngCell.EntireRow.Hidden Then
                lVisItems = lVisItems + 1
            End If
        Next
    End With
    
    ThisWorkbook.Worksheets(2).Range("E3").Value = lVisItems
End Sub

Hope that helps,

Mark

[Opv]

Thanks, mdmackillop, austenr and GTO, for the help.

austenr, your script works but I was trying to avoid the noticeable screen flicker resulting from activating Sheet1 and then Sheet2. While it is not all that noticeable in the sample sheet provided, it becomes quite noticeable in a more massive spreadsheet with numerous calculations and makes for a jumpy screen.

mdmackillop and GTO, your solutions allow me to avoid having to activate or select screen1 and, thus, the jumpy screen. I'm also pleased to learn that there is a way to avoid having to use a loop to count the visible rows. Very nice.

Thanks again everyone.

[Opv]

Try not to activate or select anything, then you don't need to worry about screen updating if you only return a single value
Always use Long, rather than integer. There is no benefit to integer and it is too "small" to hold all possible returned values.

[vba]
Option Explicit

Sub countItemsNoLooping()
Dim Rng As Range

With Sheets(1)
Set Rng = Range(.Cells(1, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
End With

Sheets(2).Range("E3").Value = Rng.Cells.Count
End Sub
[/vba]

mdmackillop,

I noticed that you use "Range(.Cells(1, 1)," etc., rather than Range("A1", Range("A1").end(xldown), etc. I am afraid I haven't spent as much time using "Cells" to define ranges. Is one way more preferable than the other in terms of efficiency?

Also, just out of curiosity, how could the above solution be modified to reference the ranges as Range("A1"), etc?

Thanks again,

Opv

[Opv]

I did some further testing pursuant to my last post, and I finally came up with something that seems to work.

[VBA]
Sub countVisible()
Dim rng As Range

With Sheets(1)
Set rng = .Range("A1", .Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With

Sheets(2).Range("E3").Value = rng.Count
End Sub
[/VBA]

If there is a better way to achieve this using "A1" rather than Cells(1,1), I would be interested in learning it.

Thanks,

Opv

[mdmackillop]

For consistency I would use
[vba]
With Sheets(1)
Set rng = Range(.Range("A1"), .Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With
[/vba]

Personally I prefer cells(i,j) as it's simpler to use with incrementing variables
eg
[vba]

With Sheets(1)
For i = 1 To 10
Set rng = Range(.Cells(1, i), .Cells(1, i).End(xlDown)).SpecialCells(xlCellTypeVisible)
Sheets(2).Range("E3").Offset(i).Value = rng.Count
Next
End With
[/vba]

[Opv]

For consistency I would use
[vba]
With Sheets(1)
Set rng = Range(.Range("A1"), .Range("A1").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With
[/vba]

Personally I prefer cells(i,j) as it's simpler to use with incrementing variables

Thanks.

[Opv]

OK, I've run into another snag....

[vba]
Sub testing()
Dim rng As Range 'rng is the source range
Dim cRng As Range

With Sheets(1)
Set rng = .Range("A4", .Range("A4").End(xlDown)).SpecialCells(xlCellTypeVisible)
Set cRng = .Range("A4", rng.Find(What:=.Range("SearchTerm"), LookIn:=xlValues, LookAt:=xlWhole))

cRng.Select
End With

End Sub
[/vba]
The above results in an error; whereas, the following does not.

[vba]
Sub testing()
Dim rng As Range 'rng is the source range
Dim cRng As Range

With Sheets(1)
Set rng = .Range("A4", .Range("A4").End(xlDown)).SpecialCells(xlCellTypeVisible)
Set cRng = rng.Find("200")

cRng.Select
End With

End Sub
[/vba]
What is causing the first example to be in error?

Opv

[mdmackillop]

I would never use your .Range("A4" ,.Range ... scenario. You are better sticking to conventional methods.
[VBA]
What:=.Range("SearchTerm")
[/VBA]
Why is this qualified? Do you have a range "SearchTerm" on more than one sheet?
Use Watch to check for values in your code and resolve these first. Try things like Debug.Print Rng.Address to make sure you have valid ranges.

[Opv]

I would never use your .Range("A4" ,.Range ... scenario. You are better sticking to conventional methods.
[vba]
What:=.Range("SearchTerm")
[/vba] Why is this qualified? Do you have a range "SearchTerm" on more than one sheet?
Use Watch to check for values in your code and resolve these first. Try things like Debug.Print Rng.Address to make sure you have valid ranges.

Point well taken. After following your suggestion, I got the desired range defined and resolved my snag. Thanks as always for your help.

Opv