We help IT Professionals succeed at work.

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

Hello,

I have an excel spreadsheet that includes a macro that involves selecting a work sheet in order to perform a number of functions. For some reason when the macro reaches the line to select the work sheet I keep getting the error message: "Run-time error '57121': Application-defined or object-defined error". I have tried numerous ways to get it to work but it does not. Only when I manually select the work sheet and then run the rest of the code does it work. Could someone please help with this?

Thanks.
Comment
Watch Question

Top Expert 2015

Commented:
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..

Author

Commented:
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.
NorieAnalyst Assistant

Commented:
We definitely need to see the code.:)
Top Expert 2015

Commented:
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...

Author

Commented:
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

Author

Commented:
The sheet name in the code is exactly the same as the worksheet name in the excel file.
Top Expert 2016

Commented:
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 

Open in new window

Regards
Top Expert 2015
Commented:
In the code you have this line as incorrect...

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

Open in new window


What you are doing here you are selecting 2 ranges in 1 go which will give you an error... I'm assuming Casrec_g10 is your name range..Either select that or select cells(55,2)

Once you clarify which range you want to select it should work...

Also like i mentioned earlier if you are running this code from different sheet module i.e. some other sheet module apart then sheet module of worksheet-->Cash_Rec.. Please move your code to new module in order to work appropriately..

Also you can use a code like this assuming you want to select cells(55,2) range and refresh query their....

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

Open in new window

NorieAnalyst Assistant

Commented:
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

Open in new window

Try something like this which can help you narrow down to the problem.

for each ws in thisworkbook.worksheets
if ws.name="Cash_Rec" then ws.select
next ws
Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

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

Do you have any ActiveX controls on the sheet?

Author

Commented:
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?

Author

Commented:
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

.
.
.
.
.
.
Top Expert 2015

Commented:
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

Open 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...

Author

Commented:
Thanks for the extra information