Running Specified code in workbook except few worksheets

[sekar]

Hi.,

I would like to run specified set of codes in each worksheet except few worksheets in the workbook.

Lets Say i have worksheets named: Data, Output. I wan run the macro code in the rest of the wok sheets.
How to do that

 

[bebo021999]

Try with IF statement, like this

working with activesheet

If activesheet.name <> "Data" and activesheet.name <>"Output" then
...main code...

or loop through sheets

For each ws in sheets
     If ws.name <> "Data" and ws.name <>"Output" then
            ...main code...
     End if
Next

 

[sekar]

Try with IF statement, like this

working with activesheet

If activesheet.name <> "Data" and activesheet.name <>"Output" then
...main code...

or loop through sheets

For each ws in sheets
     If ws.name <> "Data" and ws.name <>"Output" then
            ...main code...
     End if
Next

The code for loop through the sheets not working. it updates the current sheet only with "HG".
Can i share the excel Sheet to check.

 

[bebo021999]

The code for loop through the sheets not working. it updates the current sheet only with "HG".
Can i share the excel Sheet to check.

It depends what your code do.
In your code, try to replace activesheet by ws, may be.
If not, share the sample sheet and your full code.

 

[sekar]

Sheet Link

Untitled - Copy (2).xlsm

1 file sent via WeTransfer, the simplest way to send your files around the world

we.tl

The code as follows.

Sub sekar()
    
For Each ws In Sheets

     If ws.Name <> "Data" And ws.Name <> "Sum" Then
    
      With Range("D7", Range("D" & Rows.Count).End(xlUp))
      .Offset(, 4).Value = Evaluate(Replace("if(isnumber(match(@,{""RHS_Panel"",""Top_Panel"",""LHS_Panel"",""Bottom_Panel""},0)),""HG"","""")", "@", .Address))
   End With
        
     End If
Next
  
End Sub

 

[bebo021999]

With each ws you need to identify ws with range, otherwhile it belongs to activesheet:
Instead of:

With Range("D7", Range("D" & Rows.Count).End(xlUp))

should be

With ws.Range("D7",ws.Range("D" & Rows.Count).End(xlUp))

 

[sekar]

Hi.,

Did you checked the output.

the HG to be filled in Next opposite to the Highlighted area in Green in HG column. The red one's are placed mistakenly.

Could you check it out please

 

[bebo021999]

Try again with other MATCHJ function:

Sub sekar()
For Each ws In Sheets
    If ws.Name <> "Data" And ws.Name <> "Sum" Then
        With ws.Range("D7", ws.Range("D" & Rows.Count).End(xlUp))
            .Offset(, 4).Formula = "=IF(ISNUMBER(MATCH(D7,{""RHS_Panel"",""Top_Panel"",""LHS_Panel"",""Bottom_Panel""},0)),H$6,"""")"
            .Value = .Value ' remove this line if you want cell formula, not hardcoded value
        End With
    End If
Next
End Sub

 

[sekar]

Thanks bebo. works fine

One last thing, the formula still exist in the cell.. could you check that alone. even the .Value line is retained.

 

[bebo021999]

OK, I forgot to use.offset for the 2nd line within With...End With
By the way, try this in shorter reference:

Sub sekar()
For Each ws In Sheets
    If ws.Name <> "Data" And ws.Name <> "Sum" Then
        With ws.Range("H7:H" & ws.Range("D" & Rows.Count).End(xlUp).Row)
            .Formula = "=IF(ISNUMBER(MATCH(D7,{""RHS_Panel"",""Top_Panel"",""LHS_Panel"",""Bottom_Panel""},0)),H$6,"""")"
            .Value = .Value ' remove this line if you want cell formula, not hardcoded value
        End With
    End If
Next
End Sub