PDA

View Full Version : Solved: Referencing Another Sheet in Workbook



Opv
04-24-2010, 03:46 PM
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:

c = Sheets(1).Range("A4", Range("A4").End(xlDown)).Select


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
04-24-2010, 05:12 PM
You need to reference both elements within the range description
Dim c As Range
With Sheets(1)
Set c = Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With

Opv
04-24-2010, 05:53 PM
You need to reference both elements within the range description
Dim c As Range
With Sheets(1)
Set c = Range(.Cells(4, 1), .Cells(4, 1).End(xlDown))
End With


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
04-24-2010, 06:03 PM
It's a shortcut for
Set c = Range(Sheets(1).Cells(4, 1), Sheets(1).Cells(4, 1).End(xlDown))
As soon as you deal with two sheets, the unreferenced cell defaults to the active sheet.

It would be like

Set c = Range(Sheets(1).Cells(4, 1), Sheets(6).Cells(4, 1).End(xlDown))

Opv
04-24-2010, 06:20 PM
It's a shortcut for
Set c = Range(Sheets(1).Cells(4, 1), Sheets(1).Cells(4, 1).End(xlDown)) As soon as you deal with two sheets, the unreferenced cell defaults to the active sheet.

It would be like

Set c = Range(Sheets(1).Cells(4, 1), Sheets(6).Cells(4, 1).End(xlDown))


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
04-24-2010, 07:28 PM
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
04-24-2010, 08:19 PM
With sheet 2 activated, this worked for me

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


I moved the

.Range("A1").Select inside the With block. Your original code had it outside the With block. Also added
Sheets(2).Activate
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
04-25-2010, 03:01 AM
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.


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

GTO
04-25-2010, 04:17 AM
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
04-25-2010, 06:30 AM
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
04-25-2010, 07:08 AM
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.


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

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
04-25-2010, 10:33 AM
I did some further testing pursuant to my last post, and I finally came up with something that seems to work.


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


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
04-25-2010, 11:32 AM
For consistency I would use

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


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


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

Opv
04-25-2010, 11:43 AM
For consistency I would use

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


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


Thanks.

Opv
04-25-2010, 07:09 PM
OK, I've run into another snag....


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

The above results in an error; whereas, the following does not.


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

What is causing the first example to be in error?

Opv

mdmackillop
04-26-2010, 12:33 AM
I would never use your .Range("A4" ,.Range ... scenario. You are better sticking to conventional methods.

What:=.Range("SearchTerm")

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
04-26-2010, 08:30 AM
I would never use your .Range("A4" ,.Range ... scenario. You are better sticking to conventional methods.

What:=.Range("SearchTerm")
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