I have a macro that takes the column widths and row heights from the
worksheet I run the macro from and applies them to all the other worksheets
that aren't named a certain name. I would like to include the zoom property
in this macro, but I'm getting a 1004 error - Unable to set the Zoom property
of the PageSetup class.

The code names the active worksheet mastersht, and then goes to each sht not
named a particular name. It set the columns and rows and then bombs out on
the zoom property. Here's the code:

Dim Answer, Col, Row, Sht
Dim MasterRngC As Range
Dim MasterRngR As Range
Dim MasterRngZ As String
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's Column" & vbCrLf _
& "Widths and Row Heights to all the P# Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRngC = ActiveSheet.Range("A1", "K1")
Set MasterRngR = ActiveSheet.Range("A1", "A37")
MasterRngZ = ActiveSheet.PageSetup.Zoom

For Each Sht In Worksheets
If Sht.Name < MasterSht Then
If Sht.Name < "Index" Then
For Each Col In MasterRngC
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
For Each Row In MasterRngR
Sht.Range(Row.Address).RowHeight = Row.RowHeight
Next Row
ActiveSheet.PageSetup.Zoom = MasterRngZ
End If
End If

Next Sht

End Sub

David:

MasterRngZ as Integer

replace code
Dim MasterRngZ As Integer
--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"David" wrote:

I have a macro that takes the column widths and row heights from the
worksheet I run the macro from and applies them to all the other worksheets
that aren't named a certain name. I would like to include the zoom property
in this macro, but I'm getting a 1004 error - Unable to set the Zoom property
of the PageSetup class.

The code names the active worksheet mastersht, and then goes to each sht not
named a particular name. It set the columns and rows and then bombs out on
the zoom property. Here's the code:

Dim Answer, Col, Row, Sht
Dim MasterRngC As Range
Dim MasterRngR As Range
Dim MasterRngZ As String
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's Column" & vbCrLf _
& "Widths and Row Heights to all the P# Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRngC = ActiveSheet.Range("A1", "K1")
Set MasterRngR = ActiveSheet.Range("A1", "A37")
MasterRngZ = ActiveSheet.PageSetup.Zoom

For Each Sht In Worksheets
If Sht.Name < MasterSht Then
If Sht.Name < "Index" Then
For Each Col In MasterRngC
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
For Each Row In MasterRngR
Sht.Range(Row.Address).RowHeight = Row.RowHeight
Next Row
ActiveSheet.PageSetup.Zoom = MasterRngZ
End If
End If

Next Sht

End Sub

Thanks...that got rid of the error, but the code still does not change the
zoom to the setting from the mastersht, which is 85. They are still at 100.
Any suggestions?

"chijanzen" wrote:

David:

MasterRngZ as Integer

replace code
Dim MasterRngZ As Integer
--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"David" wrote:

I have a macro that takes the column widths and row heights from the
worksheet I run the macro from and applies them to all the other worksheets
that aren't named a certain name. I would like to include the zoom property
in this macro, but I'm getting a 1004 error - Unable to set the Zoom property
of the PageSetup class.

The code names the active worksheet mastersht, and then goes to each sht not
named a particular name. It set the columns and rows and then bombs out on
the zoom property. Here's the code:

Dim Answer, Col, Row, Sht
Dim MasterRngC As Range
Dim MasterRngR As Range
Dim MasterRngZ As String
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's Column" & vbCrLf _
& "Widths and Row Heights to all the P# Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRngC = ActiveSheet.Range("A1", "K1")
Set MasterRngR = ActiveSheet.Range("A1", "A37")
MasterRngZ = ActiveSheet.PageSetup.Zoom

For Each Sht In Worksheets
If Sht.Name < MasterSht Then
If Sht.Name < "Index" Then
For Each Col In MasterRngC
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
For Each Row In MasterRngR
Sht.Range(Row.Address).RowHeight = Row.RowHeight
Next Row
ActiveSheet.PageSetup.Zoom = MasterRngZ
End If
End If

Next Sht

End Sub

Are you sure you are changing the correct Zoom? If you are wanting to
change the screen Zoom as shown on the Standard toolbar then that is:

ActiveWindow.Zoom = 85 'or your variable

Hope the helps
Rowan

David wrote:
Thanks...that got rid of the error, but the code still does not change the
zoom to the setting from the mastersht, which is 85. They are still at 100.
Any suggestions?

"chijanzen" wrote:


David:

MasterRngZ as Integer

replace code
Dim MasterRngZ As Integer
--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"David" wrote:


I have a macro that takes the column widths and row heights from the
worksheet I run the macro from and applies them to all the other worksheets
that aren't named a certain name. I would like to include the zoom property
in this macro, but I'm getting a 1004 error - Unable to set the Zoom property
of the PageSetup class.

The code names the active worksheet mastersht, and then goes to each sht not
named a particular name. It set the columns and rows and then bombs out on
the zoom property. Here's the code:

Dim Answer, Col, Row, Sht
Dim MasterRngC As Range
Dim MasterRngR As Range
Dim MasterRngZ As String
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's Column" & vbCrLf _
& "Widths and Row Heights to all the P# Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRngC = ActiveSheet.Range("A1", "K1")
Set MasterRngR = ActiveSheet.Range("A1", "A37")
MasterRngZ = ActiveSheet.PageSetup.Zoom

For Each Sht In Worksheets
If Sht.Name < MasterSht Then
If Sht.Name < "Index" Then
For Each Col In MasterRngC
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
For Each Row In MasterRngR
Sht.Range(Row.Address).RowHeight = Row.RowHeight
Next Row
ActiveSheet.PageSetup.Zoom = MasterRngZ
End If
End If

Next Sht

End Sub

That did it! I was using the print zoom. Thanks much!!! I so much appreciate
all the help I get out here!

Many thanks again!!

"Rowan Drummond" wrote:

Are you sure you are changing the correct Zoom? If you are wanting to
change the screen Zoom as shown on the Standard toolbar then that is:

ActiveWindow.Zoom = 85 'or your variable

Hope the helps
Rowan

David wrote:
Thanks...that got rid of the error, but the code still does not change the
zoom to the setting from the mastersht, which is 85. They are still at 100.
Any suggestions?

"chijanzen" wrote:


David:

MasterRngZ as Integer

replace code
Dim MasterRngZ As Integer
--
天行å�¥ï¼Œå�›å*�以自強ä¸�æ�¯
地勢å�¤ï¼Œå�›å*�以厚德載物

http://www.vba.com.tw/plog/


"David" wrote:


I have a macro that takes the column widths and row heights from the
worksheet I run the macro from and applies them to all the other worksheets
that aren't named a certain name. I would like to include the zoom property
in this macro, but I'm getting a 1004 error - Unable to set the Zoom property
of the PageSetup class.

The code names the active worksheet mastersht, and then goes to each sht not
named a particular name. It set the columns and rows and then bombs out on
the zoom property. Here's the code:

Dim Answer, Col, Row, Sht
Dim MasterRngC As Range
Dim MasterRngR As Range
Dim MasterRngZ As String
Dim MasterSht As String
Dim Msg As String

Msg = "Do you want to apply this Worksheet's Column" & vbCrLf _
& "Widths and Row Heights to all the P# Worksheets?"
Answer = MsgBox(Msg, vbInformation + vbYesNo)
If Answer = vbNo Then Exit Sub

MasterSht = ActiveSheet.Name
Set MasterRngC = ActiveSheet.Range("A1", "K1")
Set MasterRngR = ActiveSheet.Range("A1", "A37")
MasterRngZ = ActiveSheet.PageSetup.Zoom

For Each Sht In Worksheets
If Sht.Name < MasterSht Then
If Sht.Name < "Index" Then
For Each Col In MasterRngC
Sht.Range(Col.Address).ColumnWidth = Col.ColumnWidth
Next Col
For Each Row In MasterRngR
Sht.Range(Row.Address).RowHeight = Row.RowHeight
Next Row
ActiveSheet.PageSetup.Zoom = MasterRngZ
End If
End If

Next Sht

End Sub