Setting cell height & widths to match master sheet

[claudehollett]

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

 

[pgc01]

Hi Claude

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

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:

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

...

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

Please try it.

Hope this helps
PGC

 

[claudehollett]

This workded great! I must have struggled with this for hours. Thanks