Why does my macro keep failing to select a specified work sheet?

It will be hard to help you here without looking at the code you are running..can you please post your code here..Along with details as in what line gives you an error as it will be easy to fix post that..

The code where the fail happens is listed below. Prior to reaching this line another worksheet in the same file is selected.

Sheets("Cash Rec").Select

It is so simple and straight forward that it completely puzzles me why it is failing. I have changed the name and it still does not work.

We definitely need to see the code.:)

Just a hunch that do you have this code in the sheet module and that too of some other sheet apart then cash rec .. If that's the case then can you please move the code into new module which is by going into insert-->module and pasting the code their and running from it...

Also i'm assuming the sheet name in the code is same as the actual sheet name shown in excel...

It will be hard to provide you a solution without actually looking at complete code...

Below is the code for this particular sub routine. Basically it is designed to select a work sheet, then using ODBC connections data is pulled in from an SQL database.


Sub Refresh_Opics_cash()
Dim st_date, end_date
end_date = Format(Range("date"), "yyyy-mm-dd hh:mm:ss")
st_date = Format(Range("prevdate"), "yyyy-mm-dd hh:mm:ss")


Sheets("Cash_Rec").Select

'G10
Range("CashRec_G10").Cells(55, 2).Select


With Selection.ListObject.QueryTable
    .Connection = "ODBC;Description=Opics Prod Replication;DRIVER=SQL Server;SERVER=WPPWD01V0241\OPICSREPLPROD,11000 - OPICSPLUSDB;UID=a627040;APP=Microsoft Data Access Components;WSID=DTC038137BD0E4E;Trusted_Connection=Yes"
    .CommandText = Array("SELECT BR, TRAD, VDATE, CCY, CCYAMT, CTRCCY, CTRAMT FROM OPICSPLUSDB.dbo.FXDH FXDH WHERE (VDATE>{ts '" & st_date & "'} And VDATE<={ts '" & end_date & "'}) AND (TRAD in ('IAUD'))")
    .Refresh BackgroundQuery:=False
End With

Range("A1").Select
End Sub

The sheet name in the code is exactly the same as the worksheet name in the excel file.

HI,

pls try

Sub Refresh_Opics_cash()
 Dim st_date, end_date
 end_date = Format(Range("date"), "yyyy-mm-dd hh:mm:ss")
 st_date = Format(Range("prevdate"), "yyyy-mm-dd hh:mm:ss")

 With  Sheets("Cash_Rec").Cells(55, 2).ListObject.QueryTable
     .Connection = "ODBC;Description=Opics Prod Replication;DRIVER=SQL Server;SERVER=WPPWD01V0241\OPICSREPLPROD,11000 - OPICSPLUSDB;UID=a627040;APP=Microsoft Data Access Components;WSID=DTC038137BD0E4E;Trusted_Connection=Yes"
     .CommandText = Array("SELECT BR, TRAD, VDATE, CCY, CCYAMT, CTRCCY, CTRAMT FROM OPICSPLUSDB.dbo.FXDH FXDH WHERE (VDATE>{ts '" & st_date & "'} And VDATE<={ts '" & end_date & "'}) AND (TRAD in ('IAUD'))")
     .Refresh BackgroundQuery:=False
 End With

 Range("A1").Activate
 End Sub 

Select allOpen in new window

Regards

There's no need to use Select, the start of the code could be rewritten like this.

With Sheets("Cash_Rec").Range("CashRec_G10").Cells(55, 2).ListObject.QueryTable

Select allOpen in new window

Try Sheets("Cash Rec").Activate instead of Sheets("Cash Rec").Select.

Do you have any ActiveX controls on the sheet?

Hello all,

Thanks for your work on this. I found that combining the two methods from Syed and Saurabh actually gave me the coding that worked.

Regards.

Thanks for the points.

How about sharing the code which actually worked for you?

Sub Refresh_Opics_cash()
Dim st_date, end_date
Dim ws As Worksheet, r As Range
end_date = Format(Range("date"), "yyyy-mm-dd hh:mm:ss")
st_date = Format(Range("prevdate"), "yyyy-mm-dd hh:mm:ss")

Set ws = Sheets("Cash Rec")
Set r = ws.Cells(55, 2)
 For Each ws In ThisWorkbook.Worksheets
 If ws.Name = "Cash Rec" Then ws.Select
 Next ws

.
.
.
.
.
.

f19l,

In the above code you don't need a loop or select that worksheet..Rather doing a loop.. You can simply do this...

ws.select

Now in Additional you don't have to select the worksheet..you can run the entire code without selecting which is this...

Sub Refresh_Opics_cash()
Dim st_date, end_date
Dim ws As Worksheet, r As Range
end_date = Format(Range("date"), "yyyy-mm-dd hh:mm:ss")
st_date = Format(Range("prevdate"), "yyyy-mm-dd hh:mm:ss")


Set ws = Sheets("Cash_Rec")

'G10
Set r = ws.Cells(55, 2)


With r.ListObject.QueryTable
    .Connection = "ODBC;Description=Opics Prod Replication;DRIVER=SQL Server;SERVER=WPPWD01V0241\OPICSREPLPROD,11000 - OPICSPLUSDB;UID=a627040;APP=Microsoft Data Access Components;WSID=DTC038137BD0E4E;Trusted_Connection=Yes"
    .CommandText = Array("SELECT BR, TRAD, VDATE, CCY, CCYAMT, CTRCCY, CTRAMT FROM OPICSPLUSDB.dbo.FXDH FXDH WHERE (VDATE>{ts '" & st_date & "'} And VDATE<={ts '" & end_date & "'}) AND (TRAD in ('IAUD'))")
    .Refresh BackgroundQuery:=False
End With


End Sub

Select allOpen in new window

And incase if you still want to select that worksheet.. then right below this line...

Set r = ws.Cells(55, 2)

Just add this line..

ws.select

Saurabh...

Thanks for the extra information