R
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
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