Mar 15 2021 08:56 PM - edited Mar 15 2021 09:17 PM
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 -
Please help...
Mar 16 2021 04:01 AM
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
Mar 16 2021 04:35 AM