Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude hidden sheets from formatting
I'm using this sub which operates the way it should except it formats
one sheet hidden I would like left alone, I tried fixing it using the below listed code, but I can't get it to work. Public Sub Fix_Up_Sheets() Dim SH As Worksheet For Each SH In Worksheets With SH .Rows().RowHeight = 78 .Rows(1).RowHeight = 15 .Columns("C").Insert With .Range("A1:S1") .Font.Bold = True .Font.Color = vbRed .Cells.Interior.Color = vbYellow .Columns("c").ColumnWidth = 18 .Columns("b").ColumnWidth = 15.29 .Columns("e").ColumnWidth = 15.29 End With End With Next End Sub ' this will exclude all hidden worksheets Private Sub Test() For Each ws in Worksheets If Not ws.Visible = False Then ws.Activate [actions] End If Next ws End Sub Could someone please combine the two, thanks Bart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude hidden sheets from formatting
Try this
Public Sub Fix_Up_Sheets() Dim SH As Worksheet For Each SH In Worksheets If SH.Visible = True Then 'exclude none visible With SH .Rows().RowHeight = 78 .Rows(1).RowHeight = 15 .Columns("C").Insert With .Range("A1:S1") .Font.Bold = True .Font.Color = vbRed .Cells.Interior.Color = vbYellow .Columns("c").ColumnWidth = 18 .Columns("b").ColumnWidth = 15.29 .Columns("e").ColumnWidth = 15.29 End With End With End If Next End Sub Mike "Bart" wrote: I'm using this sub which operates the way it should except it formats one sheet hidden I would like left alone, I tried fixing it using the below listed code, but I can't get it to work. Public Sub Fix_Up_Sheets() Dim SH As Worksheet For Each SH In Worksheets With SH .Rows().RowHeight = 78 .Rows(1).RowHeight = 15 .Columns("C").Insert With .Range("A1:S1") .Font.Bold = True .Font.Color = vbRed .Cells.Interior.Color = vbYellow .Columns("c").ColumnWidth = 18 .Columns("b").ColumnWidth = 15.29 .Columns("e").ColumnWidth = 15.29 End With End With Next End Sub ' this will exclude all hidden worksheets Private Sub Test() For Each ws in Worksheets If Not ws.Visible = False Then ws.Activate [actions] End If Next ws End Sub Could someone please combine the two, thanks Bart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exclude hidden sheets from formatting
On Sep 29, 11:25*am, Mike H wrote:
Try this Public Sub Fix_Up_Sheets() * Dim SH As Worksheet * For Each SH In Worksheets * If SH.Visible = True Then 'exclude none visible * * With SH * * * .Rows().RowHeight = 78 * * * .Rows(1).RowHeight = 15 * * * .Columns("C").Insert * * * With .Range("A1:S1") * * * * .Font.Bold = True * * * * .Font.Color = vbRed * * * * .Cells.Interior.Color = vbYellow * * * * .Columns("c").ColumnWidth = 18 * * * * .Columns("b").ColumnWidth = 15.29 * * * * .Columns("e").ColumnWidth = 15.29 * * * End With * * End With End If * Next End Sub Mike "Bart" wrote: I'm using this sub which operates the way it should except it formats one sheet hidden I would like left alone, I tried fixing it using the below listed code, but I can't get it to work. Public Sub Fix_Up_Sheets() * Dim SH As Worksheet * For Each SH In Worksheets * * With SH * * * .Rows().RowHeight = 78 * * * .Rows(1).RowHeight = 15 * * * .Columns("C").Insert * * * With .Range("A1:S1") * * * * .Font.Bold = True * * * * .Font.Color = vbRed * * * * .Cells.Interior.Color = vbYellow * * * * .Columns("c").ColumnWidth = 18 * * * * .Columns("b").ColumnWidth = 15.29 * * * * .Columns("e").ColumnWidth = 15.29 * * * End With * * End With * Next End Sub ' this will exclude all hidden worksheets Private Sub Test() * *For Each ws in Worksheets * * * If Not ws.Visible = False Then * * * * *ws.Activate * * * * *[actions] * * * End If * *Next ws End Sub Could someone please combine the two, thanks Bart Thanks Mike that works great. Bart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exclude Hidden Row Values | Excel Worksheet Functions | |||
Copy Worksheet and Exclude Hidden fields | Excel Worksheet Functions | |||
Exclude data from summary totals when hidden | Excel Worksheet Functions | |||
Exclude hidden workbooks from list | Excel Programming | |||
Exclude hidden data from summary totals | Excel Discussion (Misc queries) |