martin - the subroutines are indeed running in the proper (2nd)
workbook, it opens it, finds out what month it is (per current date) &
opens the correct worksheet. but before returning back to the
userform-workbook, i wanted to identify that correct worksheet (because
every month it will be a different one - however it will always be the
"active" worksheet in that workbook).

you & jim have the correct idea, i know, of setting them as objects;
that's what i was trying to do. i have a HORRIBLE time getting this
area straight (previous posts). i would definitely prefer not to use
selects & activates, but i can't make the silly things work! :)

so if i tell vba that xxxx = activeworkbook, and then switch to another
workbook, and tell it that yyyy = activeworkbook, it will keep them
straight in it's own little computer mind????
i was afraid it wouldn't remember which activeworkbook was which.
i will work on this some more.
<sigh
thanks for your help!
susan



Martin Fishlock wrote:
Susan:
Run are calling the subroutine which selects some sheet.

Call Select_Sheet

Then you try and use the activesheet sheet in myRPC which is probably not
active.

myRPCSheet = Workbooks(myRPC).ActiveSheet.Name '****this is where it
bombs....

''''

Also it is easier to use the worksheet objects rather than refering to the
names of the worksheets have a look below and see that you can refer to the
worksheet without activating it.


Sub cmdExport_click()
Dim wsMySheet As Worksheet
Dim wbMyRPC As Workbook
Dim wsMyRPC As Worksheet

Me.Hide

myFileName = ThisWorkbook.Name
myPath = ThisWorkbook.Path

mySheet = Workbooks(myFileName).ActiveSheet.Name 'this works fine
Set wsMySheet = ActiveSheet
Workbooks.Open Filename:="\\Server\users\Susan\My
Documents\Miscellaneous\Excel Help\Macro Projects-Excel\RPC Book1.xls"

myRPC = ActiveWorkbook.Name 'this works fine
Set wbMyRPC = ActiveWorkbook

'open the correct worksheet by month
Call MonthNumber
Call Select_Sheet

myRPCSheet = Workbooks(myRPC).ActiveSheet.Name '****this is where it
bombs....
Set wsMyRPCSheet = wbMyRPC.Worksheets(somename)
'switch back to the invoice workbook

Workbooks(myFileName).Activate
'then you can
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Susan" wrote:

this is from a userform code connected to one workbook. the code then
opens a 2nd workbook & selects a certain sheet based on what month it
is. then i have to switch back & forth between the 2 workbooks..... i
can qualify the sheet i need in the workbook that the userform is in,
but for some reason it won't let me qualify the sheet in the OTHER
workbook, even tho i've identified the workbook, etc.
any ideas?
xxxxxxxxxxxxxxxxxxxxx
Sub cmdExport_click()

Me.Hide

myFileName = ThisWorkbook.Name
myPath = ThisWorkbook.Path

mySheet = Workbooks(myFileName).ActiveSheet.Name 'this works fine

Workbooks.Open Filename:="\\Server\users\Susan\My
Documents\Miscellaneous\Excel Help\Macro Projects-Excel\RPC Book1.xls"

myRPC = ActiveWorkbook.Name 'this works fine

'open the correct worksheet by month
Call MonthNumber
Call Select_Sheet

myRPCSheet = Workbooks(myRPC).ActiveSheet.Name '****this is where it
bombs....

'switch back to the invoice workbook
Workbooks(myFileName).Activate

end sub
xxxxxxxxxxxxxxxxx

i hate to use "activeworkbook" to qualify that 2nd sheet, even though
the correct one IS active at that point; i wanted to use the actual
workbook name.
the error i get is "run-time error 91 - object variable or with block
variable not set".
thanks for any help.
susan