CODEWITHSUNDEEP
vbaexcel
perbrethil
perbrethil

Reputation: 131

VBA Excel deselect on another tab

I've been roaming around but I can't seem to find a suitable answer. I have a macro that saves the data on another tab in the background depending on the time by pasting the data on that tab when I paste it on my main page. The problem is that when pasting it on a tab on the background it keeps the selection active which I personally find to look a little sloppy.

Is there a way to clear the selection on a not active tab without switching back and forth to it?

For completeness a piece of the code:

'Between 8 & 9
If y >= 0.334 And y <= 0.375 And Worksheets("800").Range("B1") <> DateValue(Now) Then
    Ws8.Range("A3").PasteSpecial xlPasteAll
    Worksheets("800").Range("B1") = DateValue(Now)
    Worksheets("800").Range("C1") = TimeValue(Now)

'Between 9 & 10
ElseIf y >= 0.375 And y <= 0.417 And Worksheets("900").Range("B1") <> DateValue(Now) Then
    Ws9.Range("A3").PasteSpecial xlPasteAll
    Worksheets("900").Range("B1") = DateValue(Now)
    Worksheets("900").Range("C1") = TimeValue(Now)

Upvotes: 1

Views: 886

Answers (2)

Xabier
Xabier

Reputation: 7735

You are probably looking for Application.CutCopyMode = False as well as maybe selecting Range("A1"), maybe like below:

Application.ScreenUpdating = False

If y >= 0.334 And y <= 0.375 And Worksheets("800").Range("B1") <> DateValue(Now) Then
    Ws8.Range("A3").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    Ws8.Select
    Ws8.Range("A1").Select
    Worksheets("800").Range("B1") = DateValue(Now)
    Worksheets("800").Range("C1") = TimeValue(Now)

'Between 9 & 10
ElseIf y >= 0.375 And y <= 0.417 And Worksheets("900").Range("B1") <> DateValue(Now) Then
    Ws9.Range("A3").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    Ws9.Select
    Ws9.Range("A1").Select
    Worksheets("900").Range("B1") = DateValue(Now)
    Worksheets("900").Range("C1") = TimeValue(Now)
End If
'now select your original sheet
Sheets("Sheet1").Select
Application.ScreenUpdating = True

Upvotes: 1

TinMan
TinMan

Reputation: 7759

After you paste the value use ↓code↓ to clear the selection.

 Application.CutCopyMode = False

Direct value assignments are preferable over Range.Copy and Range().PasteSpecial xlPasteAll.

Upvotes: 1

Related Questions