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

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

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

ok.

i have the following declarations in a global module:
Public wbMyInvoices As Workbook
Public wsMyInvoiceSheet As Worksheet
Public wbMyRPC As Workbook
Public wsMyRPCSheet As Worksheet

when i run the sub, if i have
wbMyInvoices = ThisWorkbook.Name
then i get the run-time 91 error.... even though the watch says the
correct name of the workbook.

if i change it to
Set wbMyInvoices = ThisWorkbook.Name
then i get "compile error - type mismatch".

if i change it to
wbMyInvoices = ThisWorkbook
then i get no error but the watch doesn't say it grabbed the name.

so what am i doing wrong?
:)
thanks
susan



Jim Thomlinson wrote:
***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????***

That is exactly what the computer will do. Think of the objects like
pointers which point at something. If you set it to point at a specific sheet
or workbook then it will point at that until you tell it to point at
something else (regardless of wht the active sheet might be doing). You can
get back to the object but just asking the pointer what it is pointing at.
You can have as many different pointers that you need...
--
HTH...

Jim Thomlinson


"Susan" wrote:

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

ahhhhhh...

thank you for explaining it so **i** could understand it........ many
have tried, few have succeeded! ha ha! i usually learn best by doing,
but this time it just wasn't working. i had all the pieces but didn't
know how to put them together.
i will try it all again.
:)
susan

Jim Thomlinson wrote:
You dno't really need to set a object for this workbook since it is the the
one object you can always count on. It is the workbook that is running the
code.

That being said here is a quick rundown about objects. Objects are things
(like books and sheets and ranges). These things have properties (which
describe the object) and methods (things the object can do). Name is a
property of an object since it describes the object. Copy would be a method
ais that is something the the object can do.

Now on to syntax. In order to point (or cahnge what is bing pointed at) you
need to use the Set key word. So...
wbMyInvoices = ThisWorkbook
won't work as it does not use the Set key word.
Set wbMyInvoices = ThisWorkbook.Name
Won't work because you are not pointing at an object but rather you are
pointing at a property of the object.

Give this a whirl...
Set wbMyInvoices = ThisWorkbook
msgbox wbMyInvoices.name & " = " & ThisWorkbook.name

--
HTH...

Jim Thomlinson


"Susan" wrote:

ok.

i have the following declarations in a global module:
Public wbMyInvoices As Workbook
Public wsMyInvoiceSheet As Worksheet
Public wbMyRPC As Workbook
Public wsMyRPCSheet As Worksheet

when i run the sub, if i have
wbMyInvoices = ThisWorkbook.Name
then i get the run-time 91 error.... even though the watch says the
correct name of the workbook.

if i change it to
Set wbMyInvoices = ThisWorkbook.Name
then i get "compile error - type mismatch".

if i change it to
wbMyInvoices = ThisWorkbook
then i get no error but the watch doesn't say it grabbed the name.

so what am i doing wrong?
:)
thanks
susan



Jim Thomlinson wrote:
***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????***

That is exactly what the computer will do. Think of the objects like
pointers which point at something. If you set it to point at a specific sheet
or workbook then it will point at that until you tell it to point at
something else (regardless of wht the active sheet might be doing). You can
get back to the object but just asking the pointer what it is pointing at.
You can have as many different pointers that you need...
--
HTH...

Jim Thomlinson


"Susan" wrote:

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