Question related to unselect worksheet

Question

Hello Everyone,I have 3 question -&nbsp;1 --&nbsp; 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.&nbsp;2 --&nbsp;&nbsp;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.&nbsp;3 --&nbsp; Can I text to column under same function "if" ?&nbsp;Here is a screenshot of VBA code -&nbsp;&nbsp;Please help...&nbsp;

hansvogelaar · Answer

Excel&nbsp;
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.
&nbsp;
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

excel · Answer

It worked! Thank you so much sir😊😊

Forum Discussion

Question related to unselect worksheet

2 Replies

Resources