To use Select/Avivate or Not use Select/Activate

[JeffGrant]

Hi All,

Everywhere I read I am being told to avoid using Select or Activate as much as possible. And I have done that in my model. However, I have several routines like this one that refreshes a query.
If I don't use select or activate, the query does not run.

Why is this so?

Thanks in advance for the advice.......

Sub SetCurrentTime()

Sheet10.Activate
Set ws = Sheet10
With ws

If Format(Now(), "hh:mm") < Format(.Range("AQ2").Value, "hh:mm") Then
.Range("BG48").Value = Format(.Range("AQ2").Value, "hh:mm")
Else
.Range("BG48").Value = Format(Now(), "hh:mm")
End If
Set rng = .Range("BA39")
rng.ListObject.QueryTable.Refresh BackgroundQuery:=False
.Range("AR6").Value = .Range("BC48").Value

End With
Exit Sub
End Sub

 

[bstory84]

Are you saying that this code works when you utilize the Selection or Activate methods? Then doesn't work when you remove them?
Are you able to post the error you are encountering if any.
I would look at the range and the object. The range "rng" should be associated with a ListObject that contains the QueryTable. The ListObject should have a QueryTable object associated with it. If the range "rng" does not refer to a ListObject, this will cause an error.

 

[RoryA]

There is nothing in that code that would require the sheet to be activated. (the Exit Sub line is completely redundant though)

 

[JeffGrant]

Hi Rory, oops, the Exit Sub is just there because I deleted other bits an pieces.Missed that one.
I agree, there is nothing there that requires the select or activate, but the refresh simple does not woprk in this sub and others.
That is why I am confused

 

[RoryA]

I deleted other bits an pieces

Perhaps the problem is in the bits you removed...

 

[JeffGrant]

Hi Rory, I have been comint at this wrong.
it is this line which is giving the grief

.Range("AR6").Value = .Range("BC48").Value
the value will not copy accross

 

[JeffGrant]

I can copy/paste and it works. but the line of code wont.
I'll come back later. Just found a glaringly obvious oversite