same code does not workon diffrent sheet with same type of data

[pramoc]

i have one excel sheet report1 with 900 records generated on 1st of month .


then i have report 2 with 1200 records generated on 30 th of month

I run this query on report 2 it runs sucessfully but not on report1
.
str1 = "select * from [sheet2$] where profitctr in (Select pc1 FROM [pc$])"
If objRecordset.State = 1 Then objRecordset.Close

objRecordset.Open str1, objConnection, adOpenStatic, adLockOptimistic, adCmdText

only columns i am concerned with are
column d
column bp


plz help

[Simon Lloyd]

The portion of code you supplied only refers to a STRING which has been given the variable str1, however i would imagine the rest of your code refers to Report 2 (or the sheet number) therefore without specifying Report1 or the sheet number for it it will not run on Report1.

Regards,
Simon

[pramoc]

sheet2 has data on both the report.
so there should not be any changes in query right

[Simon Lloyd]

Code will only work (run query) on the sheet that is specified in it, it does not matter to Excel that data from both reports is on Sheet2......the code will have a sheet specified to work with to gather data and it will also have a sheet specified to send the data to.

The best thing you can do is post all of your code or sample workbook(s) to get the solution you need.

Remember to remove any sensitive or personal data before posting the workbook(s)

Regards,
Simon