Run macro only for specific worksheets

[mogambo]

Hello dear

I have a workbook with 30 worksheets. How do I use or execute this macro on all the 25 worksheets in a workbook in one go, thus excluding the 5 worksheets in this workbook ? In other words, I do not want this macro to be run on these 5 initial worksheets. I want it to run only on these 25 worksheets.

I gave it a try by tweaking the code below but it gave me a compile error.

Here is the code:

Sub test()
With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
Dim Rng As Range
Dim MyCell As Range
Dim c As Range
    
    Set Rng = Range("B2:B1000")
    
    For Each MyCell In Rng
        
        If MyCell.Font.ColorIndex = 3 Then
            MyCell.EntireRow.Hidden = True
        End If
    
    Next MyCell
For Each c In Range("k2:k1000")
If c.Value = 0 Then
c.EntireRow.Hidden = True
End If
Next
With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub

Can somebody help me with this ? Even a little help will be very nice, thank you.

[GTO]

Hi Mogambo,

You didn't say what the names of the sheets that you DON'T want the code to run on, so I made these up.

Also, please note that your code changes a teeny bit, as we now specify which sheet the range currently belongs to (that is - the range being set).

Hope this helps,

Mark

[vba]Option Explicit

Sub test()
Dim Rng As Range
Dim MyCell As Range
Dim c As Range
Dim wksWorksheet As Worksheet

With Application
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With

For Each wksWorksheet In ThisWorkbook.Worksheets

If Not wksWorksheet.Name = "FirstSheet" _
And Not wksWorksheet.Name = "SecondSheet" _
And Not wksWorksheet.Name = "ThirdSheet" _
And Not wksWorksheet.Name = "FourthSheet" _
And Not wksWorksheet.Name = "FifthSheet" Then

Set Rng = wksWorksheet.Range("B2:B1000")

For Each MyCell In Rng

If MyCell.Font.ColorIndex = 3 Then
MyCell.EntireRow.Hidden = True
End If

Next MyCell

For Each c In wksWorksheet.Range("k2:k1000")

If c.Value = 0 Then
c.EntireRow.Hidden = True
End If

Next
End If

Next wksWorksheet

With Application
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub[/vba]

[mogambo]

I will give it a go at night and post the feedback, Mark. Thank you, again.

[Krishna Kumar]

Hi,

[vba]Sub kTest()
Dim s(), x, i As Long, j As Long, txt As String
s = Array("Sheet1", "Sheet2") 'add those 5 sheets

For i = 1 To Worksheets.Count
x = Application.Match(Sheets(i).Name, s, 0)
If IsError(x) Then
With Sheets(i)
For j = 2 To 1000
If ((.Cells(j, 2).Font.ColorIndex = 3) * (.Cells(j, 11) = 0)) Then
txt = txt & "," & .Cells(j, 2).Address
If Len(txt) > 245 Then
txt = Mid$(txt, 2)
.Range(txt).EntireRow.Hidden = True
txt = ""
End If
End If
Next
If Len(txt) > 1 Then
txt = Mid$(txt, 2)
.Range(txt).EntireRow.Hidden = True
txt = ""
End If
End With
End If
Next
End Sub[/vba]
HTH

[mogambo]

Mark, thank you very much. It works great.

Krishna Kumar I see that you have coded a nice program but it looks very complex. In a sense it does not make use of simple VBA in layman terms. Thanks though for your efforts. Your macro did work on my worksheets but not the correct way. Apparently, it messed up my requirement.

I happy with Mark's modification.

[Krishna Kumar]

Hi,

...but not the correct way

Try this

[vba]Sub kTest()
Dim s(), x, i As Long, j As Long, txt As String
s = Array("Sheet1", "Sheet2", "Sheet3") 'add those 5 sheets
Application.ScreenUpdating = 0
For i = 1 To Worksheets.Count
x = Application.Match(Sheets(i).Name, s, 0)
If IsError(x) Then
With Sheets(i)
For j = 2 To 1000
If .Cells(j, 2).Font.ColorIndex = 3 Or .Cells(j, 11) = 0 Then
txt = txt & "," & .Cells(j, 2).Address
If Len(txt) > 245 Then
txt = Mid$(txt, 2)
.Range(txt).EntireRow.Hidden = True
txt = ""
End If
End If
Next
If Len(txt) > 1 Then
txt = Mid$(txt, 2)
.Range(txt).EntireRow.Hidden = True
txt = ""
End If
End With
End If
Next
Application.ScreenUpdating = 1
End Sub[/vba]

HTH

[sheena]

How can I code a macro that delete a sales record, shows company data and Company purchase history from a Excel worksheet am working on.

[GTO]

How can I code a macro that delete a sales record, shows company data and Company purchase history from a Excel worksheet am working on.

Greetings Sheena,

Unless I am missing something, your question is not related to this thread - as this thread had to do with performing action(s) to multiple sheets.

I think you want to post a new thread, and I would suggest you attach an example workbook for your question; as this will make it a lot easier for all to see the layout and what is wanted.

Hope this helps,

Mark