You could paste the code into each worksheet module.

Or you could place it in Thisworkbook module and change the event type to

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Would then work on all worksheets.


Gord

On Wed, 1 Aug 2007 18:12:02 -0700, MGC wrote:

How can I make this work on 3 consecutive tabs in the same workbook?

"Gord Dibben" wrote:

Thanks for the feedback.

Greg will be pleased.


Gord

On Wed, 1 Aug 2007 17:30:01 -0700, MGC wrote:

It works!!!!! Thank you sooooo much! This is the answer! Have a great
evening!

"Gord Dibben" wrote:

Can't see the codes you say "don't work" but this code from Greg Wilson works.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim c As Range, cc As Range
Dim ma As Range

With Target
If .MergeCells And .WrapText Then
Set c = Target.Cells(1, 1)
cWdth = c.ColumnWidth
Set ma = c.MergeArea
For Each cc In ma.Cells
MrgeWdth = MrgeWdth + cc.ColumnWidth
Next
Application.ScreenUpdating = False
ma.MergeCells = False
c.ColumnWidth = MrgeWdth
c.EntireRow.AutoFit
NewRwHt = c.RowHeight
c.ColumnWidth = cWdth
ma.MergeCells = True
ma.RowHeight = NewRwHt
cWdth = 0: MrgeWdth = 0
Application.ScreenUpdating = True
End If
End With
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Have cells set to wrap text and rows to autofit.


Gord Dibben MS Excel MVP


On Wed, 1 Aug 2007 16:50:02 -0700, MGC wrote:

I am working in a rather large workbook which contains 5 merged cells. These
cells must be merged and word wrapped (or if there is some other way to keep
the text flush left please let me know this as well). I need to get the rows
to automatically adjust in height persuant to the text length in the cell. I
have tried the codes in other posts but cannot get them to work. Any clues?
Am I just doing something wrong perhaps?