Macro to hide rows?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cam - 22 Apr 2008 17:11 GMT

Hello,

I have three sheets named 700, 800, 900 and other duplicate sheets named
A700, A800, A900, B700, B800, B900, etc. where 700 and A700 or B700 have the
duplicate information. Each day, I will go in sheet 700, 800 and 900 to hide
the rows that are completed (vary everyday).
How can I create a macro so that if 2 rows are hidden from sheet 700, then
it will automatically hide 2 rows from sheet A700 & B700, etc..(same for 800
and 900 sheet)? Sample of sheet 700 is:

Row A B C D E F G H I
1 Number Date Model Type ....................
2 1000 4/2/08 5000 A
3 1001 4/2/08 3000 B
4 1002 4/3/08 3000 B
5 1003 4/6/08 6000 A
6 1004 4/8/08 6000 B

Reply to this Message

Barb Reinhardt - 22 Apr 2008 19:23 GMT

Try this macro. Alt F11. Insert Module. Paste into module. (save your
workbook before you start just in case it's not what you want). This assumes
that if you have row 2 hidden on the first sheet, you want it hidden on all
sheets with a similar name, for example.

Option Explicit
Sub HideRows()
Dim aWB As Workbook
Dim WS As Worksheet

Set aWB = ThisWorkbook

For Each WS In aWB.Worksheets
Call HideRowsOnOtherSheets(WS)
Next WS

End Sub

Sub HideRowsOnOtherSheets(myWS As Worksheet)
Dim aWB As Workbook
Dim WS As Worksheet
Dim lRow As Long
Dim myRange As Range
Dim r As Range

lRow = myWS.Cells(myWS.Rows.Count, 1).Resize(1,
myWS.Columns.Count).End(xlUp).Row
Set myRange = myWS.Cells(1, 1).Resize(lRow - 1 + 1, 1)

Set aWB = myWS.Parent
For Each WS In aWB.Worksheets
If WS.Name Like "*" & myWS.Name And WS.Name <> myWS.Name Then
For Each r In myRange
If r.EntireRow.Hidden Then
WS.Cells(r.Row, r.Column).EntireRow.Hidden = True
End If
Next r
End If
Next WS

End Sub

Signature

HTH,
Barb Reinhardt

> Hello,
>
[quoted text clipped - 13 lines]
> 5 1003 4/6/08 6000 A
> 6 1004 4/8/08 6000 B

Reply to this Message