Select Range in non-selected worksheet - Possible?

[Scotster]

I understand this will seem like a strange query, I guess it may just be a little OCD kicking in, I get annoyed when I've ran some code on a worksheet and various cells are highlighted when I select the sheet for viewing. Is it possible to deselect all cells in a worksheet, or select range("A1"), when the sheet is either not activated or invisible?
At the moment I'm using the following...

with sheets("Whateversheet")
 .range("A1").copy
 .range("A1").paste
end with

Is there something a bit more straight forward or is this it?

 

[My Aswer Is This]

Will this do what you want:

Sub Select_Me()
'Modified  4/5/2019  6:50:03 AM  EDT
Range("A1").Copy Sheets(2).Range("B1")
Application.Goto Sheets(2).Range("A1")
End Sub

 

[Scotster]

Will this do what you want:

Sub Select_Me()
'Modified  4/5/2019  6:50:03 AM  EDT
Range("A1").Copy Sheets(2).Range("B1")
Application.Goto Sheets(2).Range("A1")
End Sub

Hi there,

Thanks for the reply, that almost does what I'm looking for. To be honest I'm probably being too pedantic. There are obviously many ways to skin a cat.

Things I have tried so far, but I don't like for listed reasons....

'Downfall is that manually selected ranges are always lost and A1 is defaulted to each time a sheet is activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
 Sh.Range("A1").select
End Sub

'No downfall, just doesn't seem slick/elegant to me
with sheets("Whateversheet")
 .range("A1").copy
 .range("A1").paste
end with

Sub Select_Me()
'Activates the sheet and selects the cell when I don't necessarily want to access that sheet. I could add code to revert back to the active sheet from which the code was fired but this doesn't seem elegant either.
Application.Goto Sheets(2).Range("A1")
End Sub

Like I say, I'm probably just looking for a solution to a problem that doesn't really exist. I have a TON of stuff being done (copy, paste, formulae, witchcraft) over various sheets and once complete I would just like the selections of each to be minimal so that it looks organised.

Thanks for your help

 

[My Aswer Is This]

I'm not following what your wanting.

Tell me what you do want. It's obvious what I provided is not what you wanted.

Are you saying anytime you run any code you want the active cell to be Range("A1") of the active sheet.

When you activate a sheet some cell has to be active.

Using copy and paste surely is not needed to activate a cell.

 

[My Aswer Is This]

If you saying every time you activate a sheet you want Range("A1") to be the active cell then:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on any sheet tab
Select View Code from the pop-up context menu
Double click on ThisWorkbook
Paste the code in the VBA edit window

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Modified  4/5/2019  8:12:55 AM  EDT
Range("A1").Select
End Sub

You only need to enter this code one time. You do not need to enter it in every sheet.

It works automatically on every sheet.

 

[Scotster]

I'm not following what your wanting.

Tell me what you do want. It's obvious what I provided is not what you wanted.

Are you saying anytime you run any code you want the active cell to be Range("A1") of the active sheet.

When you activate a sheet some cell has to be active.

Using copy and paste surely is not needed to activate a cell.

Ok, I'll try to explain this as best as I can.....

I have 10 sheets with the following visibility:

Sheet1 - Visible
Sheet2 - Visible
Sheet3 - Invisible

Sheet4 - Invisible

Sheet5 - Invisible

Sheet6 - Invisible

Sheet7 - Invisible

Sheet8 - Invisible

Sheet9 - Invisible

Sheet10 - Invisible

Various downloads are done via our SAP system (Sap Scripting) they are then dumped into sheets 4 through 10.

Sheet 1 is the "Front End" selection sheet
Sheet 2 is the "Working Data" sheet
Sheet 3 is the sheet used to collate and build all of the data.

During the execution the Front End sheet is re-calculated, formulae are copied and then pasted as values. This results in a large portion of the sheet having selected cells when the sheet itself hasn't been selected. The final execution lands on Sheet2.

Therefore, the fix COULD be....

Sheets("Sheet1").select
Range("A1").select
Sheets("Sheet2").select

What I was looking for is a way to select/activate range("A1") in sheet1 without actually selecting/activating the sheet. I know that the basics of this isn't allowed (can't activate/select cell from non-selected sheet). So my workaround was to simply run the following...

Sheets("Sheet1").Range("A1").copy
Sheets("Sheet1").Range("A1").pastespecial

The end result is that once the code is finished, once sheet 1 IS selected, half the sheet doesn't show active cells. Instead the front end report looks neat with only range("A1") being highlighted.

If you create a new workbook with 3 sheets (Sheet1, Sheet2, Sheet3) then use the following module it will show what I'm talking about....

Function Show_Issue()
    Sheets("Sheet2").Select
        With Sheets("Sheet1")
            .Range("A1:S100").Copy
            .Range("A1").PasteSpecial
            '.Range("A1").Select '(cannot be done)
            '.Range("A1").Activate '(Cannot be done)
            '.Range("A1:S100").Deselect '(Cannot be done)
            Application.CutCopyMode = False
        End With
        'if you now click on Sheet1 you will see a highlighted portion of the sheet.
End Function
Function WorkAround1()
    Show_Issue
    Sheets("Sheet1").Select
    Range("A1").Select
    Sheets("Sheet2").Select
End Function
Function WorkAround2()
    Show_Issue
    
        With Sheets("Sheet1")
            .Range("A1").Copy
            .Range("A1").PasteSpecial
        End With
    
    Application.CutCopyMode = False
End Function
Function WorkAround3()
    Show_Issue
    Application.Goto Sheets("Sheet1").Range("A1")
    Sheets("Sheet2").Select
End Function<strike>
</strike>

 

[My Aswer Is This]

You said:

So my workaround was to simply run the following...

Sheets("Sheet1").Range("A1").copy
Sheets("Sheet1").Range("A1").pastespecial

Doing this does not change the active cell.


​

 

[My Aswer Is This]

I hardly ever use Functions.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[Scotster]

You said:

So my workaround was to simply run the following...

Sheets("Sheet1").Range("A1").copy
Sheets("Sheet1").Range("A1").pastespecial

Doing this does not change the active cell.


​

Yes, this does exactly what I want it to do. I was just looking for a more elegant, less of a fudge, way of accomplishing it

 

[My Aswer Is This]

If that works for you then I would just stick with it. Sorry if I have no more elegant solution.