I think you have to use the activate method to activate the sheet for which you want the selection address. Becuase for each sheet you can have a different selection done.
Main Topics
Browse All TopicsSub t()
' how can I find sheets(i).selection.addres
' Currently, I Activate the sheet, get the selection.address, then Activate my original sheet.
' It is a minor problem, but I find it annoying.
' Here are examples that demonstrate several attempts that don't work
' example #1 works, but it uses the Activate method
For i = 1 To Sheets.count
Sheets(i).Activate
Debug.Print Selection.Address
Next i
' example #2 doesn't abort, but it doesn't work. For obvious reasons, it always reports the active sheet's address.
For i = 1 To Sheets.count
Debug.Print Sheets(i).Application.Sele
Next i
' example #3 gives "object doesn't support this property or method". This is because Selection is a property of the application, NOT the workbook or the worksheet.
For i = 1 To Sheets.count
Debug.Print Sheets(i).Selection.Addres
Next i
End Sub
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.

Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
OK, here are more instructions.
open a new workbook. in sheet 1 select cells a1:a2. in Sheet 2 select cells a1:c2
run the example.
Then, (and please do not be insulted by the next comment) if you don't understand my question at that point, the chances are that you you won't be able to answer my question.
The current selection applies only to the active sheet. Although Excel does remember the selection on inactive sheets, there is no way to recover that information through the Excel object model. The only workaround is to store the selection information ourselves in an accessible location as the user is working in the workbook.
Kevin
Here is just such a workaround using VBA in the workbook. The solution below solves this problem by storing the most recently selected selection and active cell in named variables. The code below, when added to the ThisWorkbook code module, saves this information every time a selection is made on a worksheet.
[Begin Code Segment]
Private Sub Workbook_SheetSelectionCha
Sh.Names.Add "Selection", Target
Sh.Names.Add "ActiveCell", ActiveCell
End Sub
[End Code Segment]
The code below, when added to a worksheet module, allows the information stored with the code above to be retrieved as a worksheet property.
[Begin Code Segment]
Public Property Get ActiveCell() As Range
On Error Resume Next
Set ActiveCell = Me.Range(Me.Names("ActiveC
End Property
Public Property Get Selection() As Range
On Error Resume Next
Set Selection = Me.Range(Me.Names("Selecti
End Property
[End Code Segment]
With the above code in place, the sample code below illustrates how to access a worksheet's selection and active cell even when it is not active and without activating it.
Set InactiveWorksheetSelection
Set InactiveWorksheetActiveCel
Kevin
Kevin, as usual, you are way ahead of me. That is a very nice solution. It is so elegant I think I am going to submit it to the Microsoft wishlist email address. The only downside is that it allows me to write programs that work fine for me, and then fail when I share my code with someone else.
Still very slick indeed
The "real" answer, and the first answer is "you can't really do what you want to do". Kevin's workaround is very educational and may come in handy for other projects, but it is serious overkill for my current needs. I will continue to simply activate the sheet that i need, then reactivate the original sheet.
The "real" answer, and the first answer is "you can't really do what you want to do". Kevin's workaround is very educational and may come in handy for other projects, but it is serious overkill for my current needs. I will continue to simply activate the sheet that i need, then reactivate the original sheet.
Business Accounts
Answer for Membership
by: spattewarPosted on 2008-07-18 at 08:39:15ID: 22036974
how do you do the selection? And do you run this macro after doing the selection?