Macros Across Worksheets

[DazHands]

Hi I am trying to create a form where different a different subject may be chosen and depending on which one, certain questions need to be asked but this is required across two separate “forms” which I have set up as “worksheet”. However, I can only seem to hide the rows I don’t want on both sheets by running two separate Macros – is there a way to just run one Macro but which hides the rows across both sheets>

What I have at present is:

Workbook “Initial Assessment” – where Cell E1 changes depending on the subject.

Sub HideRowCellNumValue()
StartRow = 81
LastRow = 556
iCol = 1
For i = StartRow To LastRow
If Cells(i, iCol).Value = Range("G1").Value Then
Cells(i, iCol).EntireRow.Hidden = False
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
End Sub

When run, this Macro hides any row between 81 – 556 where Column 1 (column A on the Sheet) does not contain the name of the subject in Cell E1

I have a second sheet in this workbook (Tab4 - Plan of Training) which I want to link to the subject in “E1” on the Initial Assessment sheet and likewise hide certain rows depending on the subject (rows 81 – 556).

However the only way I have managed it is to run a separate Macro (sort of replicating the first but changed for the applicable rows)

Sub HideRowCellNumValue()
StartRow = 81
LastRow = 556
iCol = 1
For i = StartRow To LastRow
If Cells(i, iCol).Value = Range("G1").Value Then
Cells(i, iCol).EntireRow.Hidden = False
Else
Cells(i, iCol).EntireRow.Hidden = True
End If
Next i
End Sub

Is there a way to hide the applicable rows in the second sheet based on the selection in the first with only one macro needed to be run?

Thanks

 

[rlv01]

You have posted two macros. Your explanation implies they should be different, but the two macros as-posted are exactly the same. Can you clarify?

 

[DazHands]

You have posted two macros. Your explanation implies they should be different, but the two macros as-posted are exactly the same. Can you clarify?

There are certain rows on the first worksheet that need to be hidden depending on the selection of a certain cell.
In the second sheet there are other rows that need to be hidden based on that same cell selection in the first sheet.

I can copy the selected cell to the second sheet and from there run a second macro - but I'd like to be able to just run one macro and the rows in both sheets get hidden if they are not required (based on that selection).

At present I am having to run one macro in one sheet and then the second macro in the second sheet.

Thanks in advance for any help

 

[rlv01]

At present I am having to run one macro in one sheet and then the second macro in the second sheet.

The two macros you posted are identical. So the same macro would work on either sheet. If the 2nd macro is supposed to be different for the 2nd sheet, then please post that macro so I can see how they differ.

Also, please try to use code tags when posting your code. Example:

Sub HideRowCellNumValue()
    StartRow = 81
    LastRow = 556
    iCol = 1
    For I = StartRow To LastRow
        If Cells(I, iCol).Value = Range("G1").Value Then
            Cells(I, iCol).EntireRow.Hidden = False
        Else
            Cells(I, iCol).EntireRow.Hidden = True
        End If
    Next I
End Sub

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

as it makes the code easier to read.

 

[DazHands]

My error - copied the wrong one for the first sheet.

The Macro for the first sheet is as follows (where I am trying to hide rows 11 - 226 unless the entry in the first column (column A) of the row equals the value of cell E1

Sub HideRowCellNumValue()
StartRow = 11
LastRow = 226
iCol = 1
For I = StartRow To LastRow
If Cells(I, iCol).Value = Range("E1").Value Then
Cells(I, iCol).EntireRow.Hidden = False
Else
Cells(I, iCol).EntireRow.Hidden = True
End If
Next I
End Sub

For the Second sheet the Macro I have is as follows. Here it is rows 81 - 556 which need to be hidden unless Column A equals Cell G1 (where G1 equals E1 in the first sheet):

Sub HideRowCellNumValue()
StartRow = 81
LastRow = 556
iCol = 1
For I = StartRow To LastRow
If Cells(I, iCol).Value = Range("G1").Value Then
Cells(I, iCol).EntireRow.Hidden = False
Else
Cells(I, iCol).EntireRow.Hidden = True
End If
Next I
End Sub


Does that make sense now?

Thanks

 

[rlv01]

Something like this perhaps (not tested).

Sub HideRowCellNumValue()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim CompareRange As Range
    Dim StartRow As Long, LastRow As Long, iCol As Long, I As Long

    'Set WB = ActiveWorkbook
    Set WB = ThisWorkbook
    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Initial Assessment"
            StartRow = 11
            LastRow = 226
            iCol = 1
            Set CompareRange = WS.Range("E1").Value

        Case "Tab4 - Plan of Training"
            StartRow = 81
            LastRow = 556
            iCol = 1
            Set CompareRange = WS.Range("G1").Value

        Case Else
            LastRow = 0
        End Select

        If LastRow > 0 Then
            With WS
                For I = StartRow To LastRow
                    If .Cells(I, iCol).Value = CompareRange.Value Then
                        .Cells(I, iCol).EntireRow.Hidden = False
                    Else
                        .Cells(I, iCol).EntireRow.Hidden = True
                    End If
                Next I
            End With
        End If
    Next WS
End Sub

 

[rlv01]

ps. I don't think you understood what I meant by code tags. Please read the instructions at this link:

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Starting at "Quickly Wrap Selected Code"

 

[DazHands]

Thanks for this - sadly its not working. I get a runtime error "424" Object Required - due to this line -

Set CompareRange = WS.Range("E1").Value

 

[rlv01]

Sorry, that was my mistake. It should have been

            Set CompareRange = WS.Range("E1")

and

            Set CompareRange = WS.Range("G1")

respectively

 

[DazHands]

Sadly this does not work. It does not seem to have any effect on the second worksheet and the rows I want to be seen are hidden along with those I don't want seen.

I think I will stick with running the two macros and get the staff to run them both

Thanks for trying though