Apply a code to specificsheets

[h380am]

Dear members,

I want to apply below code to specific sheets. For example I have 3 sheets and I want it to be applied for sheet1 and sheet3

[

[CODE=vba]Sub blankcells()

Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("F:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("F2:F" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[+1]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

[/CODE]

as you can see this code can be used only for activesheet.
could you please help me doing this changes ?

Thanks and best regards,

 

[Seba Robles]

If your worksheet names are non-changing (will remain the same), you can add this lines of code at the beginning:

Worksheets("worksheet name").Activate

It should then only run the rest of your code for the activated worksheet.

Hope this helps

 

[jim may]

Here's simplified code which you can adapt to your situation

Sub Foo()
Dim wsh As Worksheet
Sheets(Array("Sheet1", "Sheet3")).Select
For Each wsh In ActiveWindow.SelectedSheets
MsgBox wsh.Name    <<< Substitute your code here !!
Next wsh
Sheets("Sheet1").Select
End Sub

 

[kennypete]

Another option - this will loop through the Workbook's Sheets.

Sub Sheets()
    For ws = 1 To ThisWorkbook.Sheets.Count
        If (ThisWorkbook.Sheets(ws).Name = "Sheet1" Or _
            ThisWorkbook.Sheets(ws).Name = "Sheet3") Then
            ' Do stuff here instead of this Debug.Print line...
            Debug.Print ThisWorkbook.Sheets(ws).Name
        End If
    Next ws
End Sub

Note you may also want to consider changing .Name to .CodeName in case you want to rename the Tab that appears on the Sheet because otherwise users renaming sheets will break things.

(Note also, you will stike challenges with @jim may's code if one of the sheets is hidden. But if you prefer that approach you may also need to change "Sheets" to "Worksheets".)

 

[h380am]

@kennypete @jim may
Dears,
I've tried this but it updates only the active sheet in my excel workbook
shall I remove something in the code ?

Sub Sheets()
    For ws = 1 To ThisWorkbook.Sheets.Count
        If (ThisWorkbook.Sheets(ws).Name = "Sheet1" Or _
            ThisWorkbook.Sheets(ws).Name = "Sheet3") Then
           
        'code
            Application.ScreenUpdating = False
Dim lr As Long
With ActiveSheet
  lr = .Columns("F:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
  On Error Resume Next
  With .Range("F2:F" & lr)
    .SpecialCells(xlCellTypeBlanks).Formula = "=RC[+1]"
    .Value = .Value
  End With
  On Error GoTo 0
End With
Application.ScreenUpdating = True
        'code

        End If
    Next ws
End Sub

Thanks a lot

 

[kennypete]

That's because you are telling it to (with the line With ActiveSheet). Change that to With Worksheets(ThisWorkbook.Sheets(ws).Name) and you should be good.

 

[h380am]

@kennypete it's ok now
thank you so much for your assistance and your time