Paste formatting on hidden sheet and deselect "paste" range without activating sheet?

[lorikgator]

All,

I've searched the interwebs and all signs seem to indicate that there's no way to remove the selection range on a hidden sheet if you copy and paste formatting only unless you activate the sheet, but I'm hoping maybe there's just some clever method I've missed. Here's what I'm doing:

I'm copying and pasting JUST the formatting (some base formatting and a fair amount of conditional formatting) to a user-entry portion of a sheet (from a hidden row that acts as my master row)
I'm doing this from a main dashboard page to the sheet that the user selects with a check box (checking the check box runs the code that does the copying)
My copy/paste combo is:

.Range("A6", iColLet & "6").Copy
.Range("A13", iColLet & "1012").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
     SkipBlanks:=False, Transpose:=False

where iColLet is the last column I care about (varies from worksheet to worksheet)
It all works just as planned, but it leaves the paste range "selected" on the hidden worksheet.
Is there any way (I've tried copying and pasting a single cell after this to change the selection and Application.CutCopyMode = False which of course just deselects the copy range) to change the focus to just a single cell on the sheet where the action took place without having to activate it, select a single cell, then return to the dashboard page?

If that's not as clear as I think it is, let me know and I'll attempt to improve my explanation!

 

[mole999]

silly thought to deselect, just select somewhere else

 

[lorikgator]

I would love to, but you can only do that if it's the active sheet and the goal was not to have to activate it (simply because even with everything visible turned off, it flickers to the activated sheet and visually slows down the macro)

 

[Jonmo1]

Why does it matter if the cell remains selected after the paste?

Are you concerned that the wrong cell will be selected the next time you UNhide that sheet?
You can use a SheetActivate Event code to auto select a certain cell when the sheet is activated..

 

[lorikgator]

It only matters because the users that will be entering data into these sheets, when they go to one of the sheets that has this selection issue, will have a whole range selected. They are mostly sales people and Excel is not their strong-suit so I was hoping to avoid the additional step of them having to "unselect" the range (which I know is as simple as just selecting a single cell, but we are talking about sales people here! )

I've just started using the inelegant (but reasonably effective) solution of adding the following to the Worksheet_Activate sub for each sheet affected:

    If Selection.Rows.Count > 10 Then
        Range("A13").Select
    End If

It'll do, but if there's a more efficient or "proper" way, I'm still trying to find it.

 

[Jonmo1]

That's exactly what I was going to suggest.
The only other alternative I can imagine is directly setting the format of the destination cell based on the format of the source cell.

Similar to
Range("B1").Value = Range("C1").Value
This is commonly used instead of copy/paste special values.
It doesn't activate any cells.

But you can't just do cell.Format = cell2.Format
Unfortunately, because there are so many different elements to the cell's format.
Background color, Font Color, Number Format, Borders, Shading, Row/Column Height/Width etc...
You'd have to do each of them individually. Pain in the rear.


Anyway..

Just opinion now, take it or leave it.

This is a classic example of contributing to the ignorance of the end user.
There's no such thing as idiot proof. They will just bring out a more idiotic idiot.
If simply having to click somwhere else on the sheet is too much to ask of your users, then I fear for the future of society. Seriously.

 

[lorikgator]

Jonmo1 you are spot on! I know idiot proof is impossible but I'm doing the best I can to achieve "dummy resistant!" Thanks for your reply... if you knew the level of ignorance of the user and how much hand-holding they require (despite being terribly smart people) you would indeed feel despondent! Such is the life in sales I suppose...