Reference to selected cells in another worksheet.

[Herakles]

Selection means ActiveWorkbook.ActiveSheet.Selection to Excel

How can I make reference to the selected cells in another worksheet without activating that worksheet.

Thanks

 

[James006]

Hi,

You can test

Sub test()
MsgBox Sheet2.Range("A1").Value
End Sub

 

[Herakles]

Hi,

You can test

Sub test()
MsgBox Sheet2.Range("A1").Value
End Sub

I probably did not make my self clear enough.

It is the address of the selected cells that I need and not the values contained within them.

For example if the cells A1:B5, D2:D7 and H7:H29 are selected I want to return the $A$1:$B$5,$D$2:$D$7,$H$7:$H$29 range.

 

[James006]

Sorry ...

For the addresses .... you can use Selection.Address

 

[Herakles]

But Selection means ActiveWorkbook.ActiveSheet.Selection to Excel.

The workbook and the sheet cannot be specified as they are part of Selection.

I have to activate the sheet to get the range of selected cells for that sheet.

 

[James006]

Since you seem not to be using variables ... would you mind posting your macro ...

 

[Herakles]

How ever I do it, Selection still returns the selection in the active worksheet.

ActiveWorkbook.ActiveSheet.Selection is just a means of understanding its meaning.

I cannot even refer to the active cell in another sheet without that sheet being active.

Public Sub subSelection()
Dim rng As Range

    MsgBox Selection.Address
    
    Set rng = Selection
    
    MsgBox rng.Address
    
End Sub

 

[James006]

You are right !!!
"I cannot even refer to the active cell in another sheet without that sheet being active" ...

By definition, Active does mean Active
For your general information, when working with macros, you do not need to use Active nor Activate ...

More importantly, can you explain what you want to accomplish ...

 

[Herakles]

I understand that the term Active should refer to the active sheet or workbook but having to
activate another worksheet in order to find the ActiveCell or Selection seems strange to me.

All I want to do is to loop through all areas / cells in the selected areas / cells in another worksheet.

It's not as if I cannot do it but I have to change active sheets and then change back again.

I just have to remember to disable and then enable events just in case the Activate and Deactivate events are set up.

 

[James006]

OK ...

In order to " loop through all areas / cells in the selected areas / cells in another worksheet " there is no need to Activate anything ...

Can you post your current macro ? or explain in plain English an example of a loop you do need ?