Setting cell height & widths to match master sheet

claudehollett

Board Regular
Joined
Dec 11, 2003
Messages
89
The following code should loop through each worksheet and (1) set colmn width in col 1-52 to match master sheet, (2) set each row heigh in row 1-3 to match master sheet, and (3) set margins to match master. (1) & (3) work fine but (2) works only on the active sheet. Can someone tell me why and how to fix it. Thanks, Claude.

Sub SetToMaster()

'This routine adjusts all Worksheets in ThisWorkbook to match Master Sheet
Dim Master As Worksheet
Set Master = ThisWorkbook.Worksheets("Master")
Dim ws As Worksheet
Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> "Master" Then
For c = 1 To 52
ActiveSheet.Columns(c).ColumnWidth = Master.Columns(c).ColumnWidth
Next

For r = 1 To 3
ActiveSheet.Rows(r).RowHeight = Master.Rows(r).RowHeight
Next

With ws.PageSetup
.LeftMargin = Master.PageSetup.LeftMargin
.RightMargin = Master.PageSetup.RightMargin
.TopMargin = Master.PageSetup.TopMargin
.BottomMargin = Master.PageSetup.BottomMargin
End With

End If
Next
Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,870
Hi Claude

I cannot understand how (1) works(?). In both (1) and (2) you are always referring to the ActiveSheet:

Code:
ActiveSheet.Columns(c).ColumnWidth = Master.Columns(c).ColumnWidth 

...

ActiveSheet.Rows(r).RowHeight = Master.Rows(r).RowHeight

You must refer to the worksheet loop variable ws:

Code:
ws.Columns(c).ColumnWidth = Master.Columns(c).ColumnWidth 

...

ws.Rows(r).RowHeight = Master.Rows(r).RowHeight

Please try it.

Hope this helps
PGC
 

Watch MrExcel Video

Forum statistics

Threads
1,113,956
Messages
5,545,171
Members
410,667
Latest member
Gaexel
Top