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.
f19lAsked:
Who is Participating?
 
Saqib Husain, SyedEngineerCommented:
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
0
 
Saurabh Singh TeotiaCommented:
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..
0
 
f19lAuthor 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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
We definitely need to see the code.:)
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
f19lAuthor 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
0
 
f19lAuthor Commented:
The sheet name in the code is exactly the same as the worksheet name in the excel file.
0
 
Rgonzo1971Commented:
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
0
 
Saurabh Singh TeotiaCommented:
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

0
 
NorieVBA ExpertCommented:
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

0
 
Martin LissOlder than dirtCommented:
Try Sheets("Cash Rec").Activate instead of Sheets("Cash Rec").Select.

Do you have any ActiveX controls on the sheet?
0
 
f19lAuthor 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.
0
 
Saqib Husain, SyedEngineerCommented:
Thanks for the points.

How about sharing the code which actually worked for you?
0
 
f19lAuthor 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

.
.
.
.
.
.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
f19lAuthor Commented:
Thanks for the extra information
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.