1

I realise that it's best practice to avoid using any selections at all in your code, but this is more out of curiosity than anything else. If you select a cell and then change your sheet, the selection in the inactive sheet is remembered.

To demonstrate; if I do something like

Sub schrodingers_copy()
Range("a1").Copy
Worksheets(2).Paste
End Sub

Then I can paste cell A1 in the active sheet into whatever "selection" is currently "selected" in the inactive sheet

Is there a way to get or change this "selection"?

  • 1
    What value are you trying to change? Where the .paste is done on worksheet 2? – BruceWayne Sep 7 '16 at 17:03
  • @BruceWayne Yes. I want to change the "selection" in an inactive sheet. This short paste routine is just to demonstrate that that selection does in fact exist. – Some_Guy Sep 7 '16 at 17:27
  • 1
    To change the seleciton wouldn't you just be then setting the range? ie Worksheets(2).Range("A1").PasteSpecial – Scott Craner Sep 7 '16 at 17:30
  • You really shouldn't use Selection, but I get what you're asking. First, I'd set the selection to a range variable. Then, you can tweak that as necessary. That should be cleaner than messing around with .Select. IIRC, if you use .Select with an INACTIVE sheet, VBA tends to throw an error until you activate that sheet. – BruceWayne Sep 7 '16 at 17:33
  • 1
    From the documentation: "Destination - Optional - ...If this argument is omitted, the current selection is used...." – Comintern Sep 7 '16 at 19:55
3

Range.Cells Property:

Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property and the examples for this topic.

Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet.

my emphasis


You're not qualifying your range, that is why this is happening

Sub schrodingers_copy()
Range("a1").Copy
Worksheets(2).Paste
End Sub

Range("a1") refers to _GLOBAL.Range("a1") where _GLOBAL is referring to Activesheet.

Had you qualified the original .Range with a sheet, this wouldn't happen. It doesn't matter if you actually use .Select or not.


Examples

Run this while Sheet1 is active. Then run it again (Sheet2 will be active)

Sub BadKitty()
    Sheet1.Cells(1, 1).Select
    MsgBox Selection.Parent.Name
    Sheet2.Activate
    Selection = "What sheet is this"
    MsgBox Selection.Parent.Name
End Sub

Select C3 on Sheet4, then turn to Sheet3 and select A1. Then watch what happens here

Sub DontOpenTheBox()
    Dim x As String
    Range("A1").Select
    Sheet2.Range("B2") = Selection.Address
    Selection = "what sheet is this? "
    Sheet4.Activate
    x = Selection.Parent.Name
    MsgBox x
    Sheet3.Range("B2") = Selection.Address
End Sub
  • I think I'm missing the point you're trying to get at here. If sheet1 is active, badkitty()copies to whatever cell was the last selection made in sheet 2. If sheet 2 is active at the beginning, it fails. – Some_Guy Sep 7 '16 at 21:14
2

The MSDN documentation for Worksheet.Paste method states, regarding the optional Destination parameter:

A Range object that specifies where the Clipboard contents should be pasted. If this argument is omitted, the current selection is used.

Since the current selection is on another worksheet, only the address part of it is relevant, per the remarks section on the same page:

If you don't specify the Destination argument, you must select the destination range before you use this method.

This method may modify the sheet selection, depending on the contents of the Clipboard.

So your question entirely boils down to how Worksheet.Paste deals with its Destination parameter when it's omitted and the clipboard contains a Range object.

Now, as other answers stated, an unqualified Range call is implicitly referring to the ActiveSheet, so your clipboard contains ActiveSheet.Range("A1") after copying:

Range("a1").Copy

When you Paste though, you are qualifying the destination - implicitly; the destination must be on whichever worksheet Worksheets(2) is referring to, because that's what you're calling .Paste against and you haven't provided a Destination parameter.

Consider this:

Sheet1.Range("A1") = "test"
Sheet1.Range("A1").Copy

Sheet3.Paste 'destination is [Sheet3!A1]
Sheet3.Paste Sheet1.Range("A2") 'destination is [Sheet1!A2]

So when a Destination is provided, it doesn't matter what sheet you've qualified the Paste call with. You ask:

Is there a way to get or change this "selection"?

Yes: Provide a value for the Destination parameter!

0

If I understand your question, would a method like this work:

Sub testSelection()
' This is to show how to work with .Selection, but not actually
' using .Select/.Selection.  We're going to take the value in
' Sheet1, SELECTION, and place that value in the selection of Sheet2, B2.

Dim theSelection As Range
Set theSelection = Selection
Debug.Print theSelection.Address

Sheets("Sheet2").Activate
Sheets("Sheet2").Cells(2, 2).Value = theSelection.Value

'Now, we want to move ONE CELL RIGHT of the Sheet1 selection
Set theSelection = theSelection.Offset(0, 1)
Debug.Print theSelection.Address

End Sub

Again, it's best practice to avoid .Select whenever possible, so as soon as you can, I'd set that to a range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.