Pass ws name to another sub

  • I can't seem to find the right combination on this one.


    I need to pass the sheet name to another Sub called SetCF. If I'm using the next ws syntax, can I just pass the ws name without having to qualify it with the Set statement? I cut out all the stuff that isn't important in the second sub.



    Code
    1. Sub SetCF(mySheet As Worksheet)
    2. Dim rng As Range: Set rng = Sheets(mySheet).Range(Cells(2, 3), Cells(5, 3))
    3. With rng.FormatConditions
    4. .Delete
    5. End With
    6. Application.Goto Sheets(mySheet).Range("C2")
    7. End Sub
  • Scratch that, I found the error. In the SetCF sub, I did not fully qualify one of the Set statements. For those interested, here's the fix. The first Set statement in the SetCF sub, I left out the ws proceeding the .Cells.


    Code
    1. Sub AddFormulas()
    2. Dim ws As Worksheet
    3. For Each ws In ActiveWorkbook.Worksheets
    4. If Left(ws.Name, 1) = "Q" Then
    5. Call SetCF(ws)
    6. ws.Visible = xlSheetVeryHidden
    7. End If
    8. Next ws
    9. End Sub


    Code
    1. Sub SetCF(ws As Worksheet)
    2. Dim rng As Range: Set rng = ws.Range(ws.Cells(2, 3), ws.Cells(5, 3))
    3. With rng.FormatConditions
    4. .Delete
    5. End With
    6. Application.Goto ws.Range("C2")
    7. End Sub