Hi,

The following is a code from a module. I have declared the worksheets
as Module level variables.


Option Explicit
Option Private Module
Dim Sourcesheet As Worksheet
Dim Destinationsheet1 As Worksheet
Dim Destinationsheet2 As Worksheet
--------------------------------------------------------------------------
Sub AddInvoice
Set Sourcesheet = Worksheets("Invoice")
Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet2 = Worksheets("Invoices")
UnProtectSourcesheet
UnProtectDestinationsheet1
UnProtectDestinationsheet2

While both the Sourcesheet and Destinationsheet2 variables are set and
available later, the Destinationsheet1 returns the "Object variable
not set" error once control goes out of the sub. Immediately after
the Set line I tested it in the Immediate window and the
Destinationsheet1.name returns Worksheets("Transactions") . However
as soon as the control goes out of the AddInvoice sub, the other two
variables continue to return values but Destinationsheet1.name shows
"object not set". I am clueless.

Thanks in Advance for the help.

Regards,
Raj

Not knowing what the rest of your code is, I would suggest that you get rid
of the 'Option Private Module' line or at least rem it out and try again.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"Raj" wrote:

Hi,

The following is a code from a module. I have declared the worksheets
as Module level variables.


Option Explicit
Option Private Module
Dim Sourcesheet As Worksheet
Dim Destinationsheet1 As Worksheet
Dim Destinationsheet2 As Worksheet
--------------------------------------------------------------------------
Sub AddInvoice
Set Sourcesheet = Worksheets("Invoice")
Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet2 = Worksheets("Invoices")
UnProtectSourcesheet
UnProtectDestinationsheet1
UnProtectDestinationsheet2

While both the Sourcesheet and Destinationsheet2 variables are set and
available later, the Destinationsheet1 returns the "Object variable
not set" error once control goes out of the sub. Immediately after
the Set line I tested it in the Immediate window and the
Destinationsheet1.name returns Worksheets("Transactions") . However
as soon as the control goes out of the AddInvoice sub, the other two
variables continue to return values but Destinationsheet1.name shows
"object not set". I am clueless.

Thanks in Advance for the help.

Regards,
Raj
.

On Mar 20, 12:29*am, Gary Brown <junk_at_kinneson_dot_com wrote:
Not knowing what the rest of your code is, I would suggest that you get rid
of the 'Option Private Module' line or at least rem it out and try again.
--
Hope this helps. *
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

"Raj" wrote:
Hi,

The following is a code from a module. I have declared the worksheets
as Module level variables.

Option Explicit
Option Private Module
Dim Sourcesheet As Worksheet
Dim Destinationsheet1 As Worksheet
Dim Destinationsheet2 As Worksheet
--------------------------------------------------------------------------
Sub AddInvoice
Set Sourcesheet = Worksheets("Invoice")
Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet2 = Worksheets("Invoices")
UnProtectSourcesheet
UnProtectDestinationsheet1
UnProtectDestinationsheet2

While both the Sourcesheet and Destinationsheet2 variables are set and
available later, the Destinationsheet1 returns the "Object variable
not set" error once control goes out of the sub. *Immediately after
the Set line I tested it in the Immediate window and the
Destinationsheet1.name returns *Worksheets("Transactions") . However
as soon as the *control goes out of the AddInvoice sub, the other two
variables continue to return values but Destinationsheet1.name shows
"object not set". I am clueless.

Thanks in Advance for the help.

Regards,
Raj
.

Tried that. The variable is not retaining the worksheet.

The code is for this: The Invoice sheet has Invoice data that is to be
written to the destination sheets which are protected. The code after
setting the sheets proceeds to unprotect the sheets . It is failing at
this point for Destinationsheet2 while it is working fine for the
other two sheets.

Regards,
Raj.

Change the code to refer to the worksheet by it's codename:

'Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet1 = Worksheets(Sheet2) 'your codename

If this works, then there is some mismatch in the spelling of the name
"Transactions".

Mike F
"Raj" wrote in message
...
On Mar 20, 12:29 am, Gary Brown <junk_at_kinneson_dot_com wrote:
Not knowing what the rest of your code is, I would suggest that you get
rid
of the 'Option Private Module' line or at least rem it out and try again.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

"Raj" wrote:
Hi,

The following is a code from a module. I have declared the worksheets
as Module level variables.

Option Explicit
Option Private Module
Dim Sourcesheet As Worksheet
Dim Destinationsheet1 As Worksheet
Dim Destinationsheet2 As Worksheet
--------------------------------------------------------------------------
Sub AddInvoice
Set Sourcesheet = Worksheets("Invoice")
Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet2 = Worksheets("Invoices")
UnProtectSourcesheet
UnProtectDestinationsheet1
UnProtectDestinationsheet2

While both the Sourcesheet and Destinationsheet2 variables are set and
available later, the Destinationsheet1 returns the "Object variable
not set" error once control goes out of the sub. Immediately after
the Set line I tested it in the Immediate window and the
Destinationsheet1.name returns Worksheets("Transactions") . However
as soon as the control goes out of the AddInvoice sub, the other two
variables continue to return values but Destinationsheet1.name shows
"object not set". I am clueless.

Thanks in Advance for the help.

Regards,
Raj
.

Tried that. The variable is not retaining the worksheet.

The code is for this: The Invoice sheet has Invoice data that is to be
written to the destination sheets which are protected. The code after
setting the sheets proceeds to unprotect the sheets . It is failing at
this point for Destinationsheet2 while it is working fine for the
other two sheets.

Regards,
Raj.

I think you meant:

Set Destinationsheet1 = Sheet2 'your codename

Where Sheet2 is the codename for a sheet in the same workbook as the code.



Mike Fogleman wrote:

Change the code to refer to the worksheet by it's codename:

'Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet1 = Worksheets(Sheet2) 'your codename

If this works, then there is some mismatch in the spelling of the name
"Transactions".

Mike F
"Raj" wrote in message
...
On Mar 20, 12:29 am, Gary Brown <junk_at_kinneson_dot_com wrote:
Not knowing what the rest of your code is, I would suggest that you get
rid
of the 'Option Private Module' line or at least rem it out and try again.
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

"Raj" wrote:
Hi,

The following is a code from a module. I have declared the worksheets
as Module level variables.

Option Explicit
Option Private Module
Dim Sourcesheet As Worksheet
Dim Destinationsheet1 As Worksheet
Dim Destinationsheet2 As Worksheet
--------------------------------------------------------------------------
Sub AddInvoice
Set Sourcesheet = Worksheets("Invoice")
Set Destinationsheet1 = Worksheets("Transactions")
Set Destinationsheet2 = Worksheets("Invoices")
UnProtectSourcesheet
UnProtectDestinationsheet1
UnProtectDestinationsheet2

While both the Sourcesheet and Destinationsheet2 variables are set and
available later, the Destinationsheet1 returns the "Object variable
not set" error once control goes out of the sub. Immediately after
the Set line I tested it in the Immediate window and the
Destinationsheet1.name returns Worksheets("Transactions") . However
as soon as the control goes out of the AddInvoice sub, the other two
variables continue to return values but Destinationsheet1.name shows
"object not set". I am clueless.

Thanks in Advance for the help.

Regards,
Raj
.

Tried that. The variable is not retaining the worksheet.

The code is for this: The Invoice sheet has Invoice data that is to be
written to the destination sheets which are protected. The code after
setting the sheets proceeds to unprotect the sheets . It is failing at
this point for Destinationsheet2 while it is working fine for the
other two sheets.

Regards,
Raj.

--

Dave Peterson