Question related to unselect worksheet

Frequent Contributor

Hello Everyone,

I have 3 question - 

1 --  Currently my coding can make "Good" in all worksheet except for DATA worksheet.

my concern is I don't want "good" in "DATA, DATA1 and DATA2" worksheet.
but appear in all the other worksheet.

 

2 --  I wish to text to column for all the worksheet except worksheet name " data, manage and instruction" but I don't know how to write it.

 

3 --  Can I text to column under same function "if" ?

 

Here is a screenshot of VBA code - 

Web capture_16-3-2021_9223_www.udemy.com.jpeg

 

Please help...

 

2 Replies

@Zan_Hanifee 

You have to make sure that you refer to ws.Range(...) in the loop. If you simply use Range(...), it refers to the active sheet instead of to ws.

 

Sub Text2Column()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
            Case "Data", "Manage", "Instruction"
                ' Skip these sheets
            Case Else
                ws.Range(ws.Range("A9"), ws.Range("A9").End(xlDown)).TextToColumns _
                    Destination:=ws.Range("A9"), DataType:=xlDelimited, _
                    Space:=True, Semicolon:=True, Tab:=True, Comma:=True
                ws.Range("B:I").EntireColumn.AutoFit
        End Select
    Next ws
End Sub
It worked! Thank you so much sir