Question about private subs

[Guest]

I have worksheet that I want to trigger a macro when a user selects a value
from a pivot table.

For the most part, the macro works fine except I also want to delete a range
of rows within a different tab in the workbook, but in the macro when I
switch to the other tab and set the range to delete, I get a "Run-time error
1004 - Application defined or object defined error".

Am I not able to access another tab from private sub?

Any help would be greatly appreciated.

Thanks, Mary-Lou

 

[Guest]

I have seen this occur when you select another tab and address the range only
without the worksheet reference using code in a macro which has been called
by an event and is in one of the Sheets areas. (I think it is because Excel
is confused about which worksheet it is addressing.)

After selecting the worksheet one would think that you should be able to
address a range simply as Range("A1:A30"). However, try
Sheets("Sheet1").Range("A1:A30") or ActiveSheet.Range("A1:A30"). I found that
this fixes the problem.

Regards,

OssieMac

 

[Guest]

Thanks, will give that a try.

I have seen this occur when you select another tab and address the range only
without the worksheet reference using code in a macro which has been called
by an event and is in one of the Sheets areas. (I think it is because Excel
is confused about which worksheet it is addressing.)

After selecting the worksheet one would think that you should be able to
address a range simply as Range("A1:A30"). However, try
Sheets("Sheet1").Range("A1:A30") or ActiveSheet.Range("A1:A30"). I found that
this fixes the problem.

Regards,

OssieMac

 

[Guest]

Hi again Mary-Lou,

I just did a test with the following code in a sub as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)
'Called from change in Sheet1

Sheets("Sheet2").Select

'The following row fails even though the worksheet is selected
'However, it works fine in a normal module.
Rows("3:5").Delete


ActiveSheet.Rows("3:5").Delete 'This works

'This is the preferred method without selecting the worksheet
Sheets("Sheet2").Rows("3:5").Delete

Regards,

OssieMac

 

[Guest]

Ok thanks.

Hi again Mary-Lou,

I just did a test with the following code in a sub as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)
'Called from change in Sheet1

Sheets("Sheet2").Select

'The following row fails even though the worksheet is selected
'However, it works fine in a normal module.
Rows("3:5").Delete


ActiveSheet.Rows("3:5").Delete 'This works

'This is the preferred method without selecting the worksheet
Sheets("Sheet2").Rows("3:5").Delete

Regards,

OssieMac