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
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
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!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.