Macro won't run under 'For each'

[Roddie]

I have a macro (vldtn_rollover) which calls six or seven other macros in
turn to rearrange data on a single sheet using lots of cut and paste. Run on
a single worksheet, it works well.

But each workbook has eight or ten worksheets, so I tried setting up

Public Sub AllVldtn_rollover()
For Each w In Worksheets
w.Activate
vldtn_rollover
Next w
End Sub

but it chokes straightaway in the first macro called by vldtn_rollover,
complaining that "This operation requires the merged cells to be identically
sized."

I'm baffled about why this runs OK on one single worksheet, but chokes on
the first one when I try to loop it through several worksheets.

I'd appreciate any advice. Thanks

Roddie Grant

 

[Edwin Tam]

I think some of the cell in at least one of the worksheets are merged. When
the macro copy and paste the cells, the source cells are of different "shape"
(some cells are merged but some are not.)

This may not be problem with your macros. Instead, the inconsistent cell
format of the ranges in the worksheets.

You can try this:
- Go to each of the worksheets, select all cells. (Command A)
- From the Format menu, choose Cells.
- In the "Alignment" tab, make sure the "Merge Cells" checkbox is unchecked.
- Click OK.

This will ensure there is no merged cells in your worksheets. Then, reapply
your macros and they should work well. If not, you may need to give up the
copy-paste approach and directly apply values to cells by the .VALUE
property. For example, Range("A1").value = Range("D1").value.

Regards,
Edwin Tam
(e-mail address removed)
http://www.vonixx.com

 

[Roddie]

Thanks for the reply - I hadn't correctly understood the meaning of "Merged"
in the error message.

The macro is working on all sheets, including hidden sheets which do have
merged cells. I've added a test for w.Visibility = True, and it now works
fine.

Many thanks

Roddie Grant

 

[Bob Greenblatt]

I'm not sure I quite understand. Is there a vldtn_rollover macro in each
workbook? Do you want to run this for each open workbook? Or, is the
problem that the macro can't reference other sheets in the workbook? As
Edwin said, your macro probably does not properly qualify the sheet it
intends to act upon.

 

[Roddie]

I was trying to get the macro to run on each sheet in a workbook. There are
several workbooks, but I'll run the macro separately for each one - it was
just getting tedious running it for each individual worksheet.

Now that I've put in a check for a worksheet being hidden, it runs fine
within a workbook.

Thanks

Roddie Grant