VBA/Excel: how to reference an "activecell" in an "inactive" sheet 1

[captsnappy]

Newbie to VBA. Searched this forum but cannot find this answer.

When I click between sheets in Excel, the active cell is "remembered" by Excel it seems. I want to paste something via VBA from the active sheet (sheetB) into the active cell in SheetA without knowing what the "active cell" in SheetA is.

Is there a property something like Worksheet("SheetA").ActiveCell when SheetA is NOT active?


Sorry if I am not explaining this sufficiently.

 

[SkipVought]

Hi,

Forum707 is a better place to get VBA questions addressed.

You cannot reference ActiveCell on an sheet other than the ActiveSheet.

What is the PROCESS that caused that cell to be selected?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Hoaokapohaku]

I would think that what you want to do is possible, but I've done some checking and it doesn't seem to be so. However, the code below will accomplish what you are trying to do. As a VBA newbie, it's good you are trying to find approaches that avoid selecting or activating sheets or ranges, but in this case I don't think it is possible.

Sub CopyToSheetA()
Dim rngActiveRange As Range

Application.ScreenUpdating = False
Worksheets("SheetA").Activate
Set rngActiveRange = ActiveCell
Worksheets("SheetB").Activate
Worksheets("SheetB").Range("A1").Copy Destination:=rngActiveRange
Application.ScreenUpdating = True

End Sub

 

[Hoaokapohaku]

Here is another way that does not require activating the sheet, but there are several steps to set it up.

Step 1 - Put this code in the "ThisWorkbook" object of your project. This is the only time any code will have to activate the SheetB. If you want the workbook to open with a different sheet active, add a line following "GetDestAddy" to activate that sheet.

Private Sub Workbook_Open()
    Sheets("sheetA").Activate
    GetDestAddy
End Sub

Step 2 - Put the following statement at the very top of a module where you will put the rest of the code (provided in following steps):

Public SheetAActive As Range

Step 3 - Put the following code for two macros in the same module:

Sub CopyToSheetA_Alt()
    Application.ScreenUpdating = False
    Worksheets("SheetB").Range("A1").Copy Destination:=SheetAActive
    Application.ScreenUpdating = True    
End Sub

Sub GetDestAddy()
        Set SheetAActive = ActiveCell
End Sub

That should do it. Now every time you open the workbook, or change the active cell on sheetB, the variable "SheetAActive" will be set with range of the active cell in sheetB, and that variable will be available for use in other macros until either you close the workbook, close excel, or run the "Set SheetAActive = Nothing" statement in a macro. The macro "CopyToSheetA_Alt" will run by whatever means you choose to run it, and will copy SheetB.range("a1") to the range object, "SheetAActive", which will always be the range of the last active cell in SheetA.

 

[Hoaokapohaku]

Ooooops...FORGOT A STEP...

I'll call it Step 1B, but it doesn't matter what order you apply the steps.

Put the following in the Excel Object for SheetB:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    GetDestAddy
End Sub

 

[SkipVought]

Tell us WHAT it is that you are trying to accomplish, rather than the HOW that you think it ought to be done.

This ActiveCell thing, is a HOW, not a WHAT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[captsnappy]

While I am ingesting Hoaokapohaku's code suggestion, what I am trying to do is this:

I want to copy data from SheetB while on SheetB to whatever cell is "active" on SheetA. By "active" I mean whatever cell had the focus on SheetA when I left that sheet.

 

[captsnappy]

The process that caused the cell on SheetA to be selected is just wherever the user had the cursor (cursor?) when they left SheetA.

 

[SkipVought]

I would use the SelectionChange event on the inactivesheet to store the address of the selection. Use that address to paste to.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[captsnappy]

Thank you, Hoaokapohaku. Your first suggestion led me to my solution. Really simpler than I was trying to make it. The application.screenupdating=False was key: after Screenupdating=false I just selected SheetA then stored ActiveCell to a var then Selected SheetB and did the copy. Thansk! (That's "Thanks" in Russian, I suppose...)

Trey

 

[Hoaokapohaku]

Trey,

I'm glad it worked for you! And mahalo (thanks in Hawaiian) for letting me know. I enjoy playing with VBA, and it's gratifying to know I helped someone.

You might get more efficient yet if you considered Skip's questions about what are you trying to accomplish. I addressed your question exactly how you asked it, but if you consider the reasoning that you use when you manually select the cell in SheetA, you can probably code that line of reasoning so that you do not need to select a cell at all. VBA can do it for you, and the coding for this type of thing is usually simple.

Les