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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
NorieAnalyst Assistant Commented:
We definitely need to see the code.:)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.