running macro in entire workbook

  • Thread starter Thread starter Paul_of_Abingdon
  • Start date Start date
P

Paul_of_Abingdon

I have written a macro to hide rows if the value in a row is false. I put
this macro in "ThisWorkbook" code window, instead of a worksheet window. The
Macro starts after an user makes a selction from drop down Control box in
worksheet ONE. This macro works fine. I have 50 more sheets with same row
information. I would like to hide those rows as well when the macro is
executed. Other sheet does not have drop down control box. When True or false
value changes in rows of sheet one after user's selection, the value in other
sheets also changes because I have used PASTE LINK in other sheets. Is there
a way to hide rows in other sheet when macro is excecuted in sheet one? Here
is my Macro to hide rows with control box:

Sub HURows()
BeginRow = 9
EndRow = 211
ChkCol = 8

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "False" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Private Sub ComboBox1_Change()

End Sub
 
Is there a way to hide rows in other sheet when macro is excecuted in
The macro would have to be expanded to operate on each sheet in turn. You
might create a macros that activates each sheet and then calls this macro.

--
Jim
message |I have written a macro to hide rows if the value in a row is false. I put
| this macro in "ThisWorkbook" code window, instead of a worksheet window.
The
| Macro starts after an user makes a selction from drop down Control box in
| worksheet ONE. This macro works fine. I have 50 more sheets with same row
| information. I would like to hide those rows as well when the macro is
| executed. Other sheet does not have drop down control box. When True or
false
| value changes in rows of sheet one after user's selection, the value in
other
| sheets also changes because I have used PASTE LINK in other sheets. Is
there
| a way to hide rows in other sheet when macro is excecuted in sheet one?
Here
| is my Macro to hide rows with control box:
|
| Sub HURows()
| BeginRow = 9
| EndRow = 211
| ChkCol = 8
|
| For RowCnt = BeginRow To EndRow
| If Cells(RowCnt, ChkCol).Value = "False" Then
| Cells(RowCnt, ChkCol).EntireRow.Hidden = True
| Else
| Cells(RowCnt, ChkCol).EntireRow.Hidden = False
| End If
| Next RowCnt
| End Sub
|
| Private Sub ComboBox1_Change()
|
| End Sub
|
|
 
I am new with Macro. Would you help me how I can expand macro to operate in
each sheet in turn and create a macro that activates each sheet and then call
this macro?

Thank you in advance.
 
I am new with Macro. Would you help me how I can expand macro to operate in
each sheet in turn and create a macro that activates each sheet and then call
this macro?

Thank you in advance.
 
Sub HideAllRows()
Application.ScreenUpdating = False
Worksheets("PutSheet1NameHere").Activate
HURows
Worksheets("PutSheet2NameHere").Activate
HURows
'Etc
End Sub

If the worksheets are in consecutive order this can be simplified

Sub HideAllRows2()
Dim Counter as Integer
Application.ScreenUpdating = False
For Counter = 1 to 50 'Chg to, say, 5 to 54 if first sht to print is #5
Worksheets(Counter).Activate
HURows
Next
End Sub

--
Jim
message |I am new with Macro. Would you help me how I can expand macro to operate in
| each sheet in turn and create a macro that activates each sheet and then
call
| this macro?
|
| Thank you in advance.
|
|
| "Jim Rech" wrote:
|
| > >>Is there a way to hide rows in other sheet when macro is excecuted in
| > >>sheet one?
| >
| > The macro would have to be expanded to operate on each sheet in turn.
You
| > might create a macros that activates each sheet and then calls this
macro.
| >
| > --
| > Jim
| > message | > |I have written a macro to hide rows if the value in a row is false. I
put
| > | this macro in "ThisWorkbook" code window, instead of a worksheet
window.
| > The
| > | Macro starts after an user makes a selction from drop down Control box
in
| > | worksheet ONE. This macro works fine. I have 50 more sheets with same
row
| > | information. I would like to hide those rows as well when the macro is
| > | executed. Other sheet does not have drop down control box. When True
or
| > false
| > | value changes in rows of sheet one after user's selection, the value
in
| > other
| > | sheets also changes because I have used PASTE LINK in other sheets. Is
| > there
| > | a way to hide rows in other sheet when macro is excecuted in sheet
one?
| > Here
| > | is my Macro to hide rows with control box:
| > |
| > | Sub HURows()
| > | BeginRow = 9
| > | EndRow = 211
| > | ChkCol = 8
| > |
| > | For RowCnt = BeginRow To EndRow
| > | If Cells(RowCnt, ChkCol).Value = "False" Then
| > | Cells(RowCnt, ChkCol).EntireRow.Hidden = True
| > | Else
| > | Cells(RowCnt, ChkCol).EntireRow.Hidden = False
| > | End If
| > | Next RowCnt
| > | End Sub
| > |
| > | Private Sub ComboBox1_Change()
| > |
| > | End Sub
| > |
| > |
| >
| >
| >
 
Jim:

The first option you had suggested, where I supply worksheet name, worked.
But when I am trying to use the second option with Counter, it did not work.

I build a new workbook. So, worksheet has consecutive number. I have also
added a new macro to name the worksheet based on the cell value enter by the
user. Since there is going to be more than 50 worksheet, I needed to add this
macro. So user will know which worksheet to open. Since the worksheet name
changes, I can not name the tab in the macro, can I? Here is the full VB code:

Private Sub ComboBox1_Change()

End Sub

Sub HURows()
BeginRow = 6
EndRow = 208
ChkCol = 1

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "FALSE" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Sub HideAllRows()
Dim Counter As Integer
Application.ScreenUpdating = False
For Counter = 1 To 60 'Chg to, say, 5 to 54 if first sht to print is #5
Worksheets(Counter).Activate
HURows
Next
End Sub


Sub Name_Tab()
For Each ws In Worksheets
ws.Name = ws.Range("F1").Value
Next
End Sub
 
Jim:

Never mind. It was my mistake. I worked. I had a typo inside my worksheet IF
statement. I forgot to enter "" for True False value. Thank you again.

Paul
 
Back
Top