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
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