Log in

View Full Version : [SOLVED:] Run Same VBA Macro Code On Multiple Sheets



NYGiantsGuy1
04-04-2016, 07:49 PM
Hi,

Im a total novice and did not know where else to turn so here goes. I found this code to hide blank rows and it works but I need it to hide rows on select worksheets in my file.

The worksheets it needs to run on are"SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12" , and "SHEET13".


Sub hideEmptyRows2()


Application.ScreenUpdating = False


For i = 3 To 120
If ActiveSheet.Cells(i, 1) = "" Then
ActiveSheet.Cells(i, 1).EntireRow.Hidden = True
End If
Next i


Application.ScreenUpdating = True


End Sub

Thanks in advance!

Best,
Aidan

snb
04-05-2016, 12:45 AM
Sub M_snb()
for j=6 to 13
sheets("sheet" & j).columns(1).specialcells(4).entirerow.hidden=-1
next
End Sub

Paul_Hossler
04-05-2016, 04:27 AM
Another way

Personally, if there were a indicator on the worksheet (e.g. 'If ws.Range("A1") = "SOMETHING"') that you could trigger off of, it would be more flexible since you wouldn't need to keep editing the list. What happens if you add Sheet14 or rename Sheet9?



Option Explicit
Sub hideEmptyRows2()
Dim v As Variant

Application.ScreenUpdating = False
For Each v In Array("SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12", "SHEET13")
On Error Resume Next ' in case there are no blank cells
Worksheets(v).Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
Next
Application.ScreenUpdating = True
End Sub



Sub hideEmptyRows2a()
Dim ws As Worksheet


Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1") = "SOMETHING" Then
On Error Resume Next ' in case there are no blank cells
ws.Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
End If
Next
Application.ScreenUpdating = True
End Sub

NYGiantsGuy1
04-05-2016, 07:02 AM
Hi snb,

When I try to execute the code I get a "runtime error 9 "subscript out of range". Does it make a difference if my sheets have names i.e. Sheet 5 = Financial Proof?

Thanks for your help!

-Aidan

NYGiantsGuy1
04-05-2016, 07:08 AM
Hi Paul,


Im a little confused about what you mean by If ws.Range("A1") = "SOMETHING" Then . So Column A will have employee names that will always be different. Is there something generic I can enter for "soemthing"? Like "IsNotBlank"?


I tried entering a name into this just for testing purposes, If ws.Range("A1") = "Jeff" Then, but when I run the code you posted nothing happens.


Thanks for your help!


-Aidan

snb
04-05-2016, 07:35 AM
The name of the sheets must be, like you indicated yourself, "sheet6", "sheet7", etc.
Post a file to show what your workbook looks like.


Sub M_snb()
on error resume next

For each sh in sheets
sh.columns(1).specialcells(4).entirerow.hidden=-1
Next
End Sub

Paul_Hossler
04-05-2016, 08:02 AM
Hi Paul,

Im a little confused about what you mean by If ws.Range("A1") = "SOMETHING" Then . So Column A will have employee names that will always be different. Is there something generic I can enter for "something"? Like "IsNotBlank"?
I tried entering a name into this just for testing purposes, If ws.Range("A1") = "Jeff" Then, but when I run the code you posted nothing happens.
Thanks for your help!
-Aidan

Since your original test started in row 3, I was suggesting that if there were some kind of marker or content that could be used as a test

For example, many times a sheet would have something like "Date: 3/1/2016 Report of stuff" in A1

Instead of hard coding sheet names ("SHEET5") which will break the macro when you rename it to "Financial Proof", it's better to make the macro a little smarter


So something like



For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Range("A1").Value,5) = "Title" Then



or



For Each ws In ActiveWorkbook.Worksheets
If Len(ws.Range("A1").Value) > 0 Then


would only process the sheets that pass the text

THAT's the reason I hate to hard code names like that into a macro




The worksheets it needs to run on are"SHEET5", "SHEET6", "SHEET7", "SHEET8", "SHEET9", "SHEET10", "SHEET11", "SHEET12" , and "SHEET13".


Are you saying that the names of the worksheets as shown at the bottom of the window are really not "SHEET5", etc.?

NYGiantsGuy1
04-05-2016, 08:53 AM
Hey sbn,

Here is a file.

15838

Hey Paul,

That is correct the sheets at the bottom of the window all have unique names. I uploaded a file for reference.

Paul_Hossler
04-05-2016, 08:59 AM
Try this





Option Explicit

Sub hideEmptyRows2b()
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
If ws.Range("A1") = "Census Data" Then
On Error Resume Next ' in case there are no blank cells
ws.Range("A3:A120").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
On Error GoTo 0
End If
Next

Application.ScreenUpdating = True

End Sub

NYGiantsGuy1
04-05-2016, 06:37 PM
Thanks Paul! This works perfectly!