Object variable not set error

[Raj]

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

 

[Gary Brown]

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.

 

[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

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.

 

[Mike Fogleman]

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

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

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]

I think you meant:

Set Destinationsheet1 = Sheet2 'your codename

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