0

I have a workbook where I want to:

  1. Add a formula to specific column (column T)
  2. Loop through different tabs and add formula from 1.
  3. Filter all those columns descending.

The code only runs on the first sheet.

Dim N As Long
Dim wsName As String

For N = 1 To ThisWorkbook.Sheets.Count
    wsName = ThisWorkbook.Worksheets(N).Name

    If Len(wsName) = 3 Then
        'command
        Call blank
        Call hide

    Else 'do nothing

    End If
Next N

Sub blank()
    Range("T2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-3]<>"""",RC[-2]="""",""FALSE"")"
    Range("T2").Select
    Selection.AutoFill Destination:=Range("T2:T6999")
End Sub

Sub hide()
    Columns("T:T").Select
    Selection.EntireColumn.Hidden = True
End Sub
Community
  • 1
  • 1
Al5145
  • 27
  • 5

1 Answers1

0

1) I'm not sure you need two separate procedures, but anyway one approach is to add a worksheet parameter to the subs you are calling.

2) Your code never changed the active sheet which by default is what is operated on; hence only the first sheet worked.

3) I suggest reading this How to avoid Select.

Sub x()

Dim N As Long
Dim wsName As String

For N = 1 To ThisWorkbook.Sheets.Count
    wsName = ThisWorkbook.Worksheets(N).Name
    If Len(wsName) = 3 Then
        'command
        blank wsName
        hide wsName
    End If
Next N

End Sub

Sub blank(s As String)

Worksheets(s).Range("T2:T6999").FormulaR1C1 = "=IF(RC[-3]<>"""",RC[-2]="""",""FALSE"")"

End Sub

Sub hide(s As String)

Worksheets(s).Columns("T:T").EntireColumn.Hidden = True

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • I received an error when adding the wsName. Error is below. Compile error: wrong number of arguments or invalid property assignment. I tried to use call blank (wsName) as well and same error. – Al5145 Dec 06 '18 at 12:57
  • Works for me. On which line? – SJR Dec 06 '18 at 13:02
  • Thanks! You're right, it works. I'll go over the suggested reading. thanks again! – Al5145 Dec 06 '18 at 13:19
  • 1
    My pleasure. Perhaps you could get in the habit of accepting answers - you don't seem to have done so so far? – SJR Dec 06 '18 at 13:24