Skip to main content

Return to Answer

added 26 characters in body
Source Link
Daniel
Daniel
  • 13.1k
  • 3
  • 39
  • 64

I was trying to figure out what you were trying to do with method 2, and I could not. But just because, here's a working version that does not use for each.

Sub format_worksheets() dim x as Integer For x = 1 To Sheets.Count With Sheets(x) .Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, TrailingMinusNumbers:=True End With Next x End Sub

Note that this will error if any sheet has no data in Column A. I also left out the select statements because it's best practice to avoid them if they are not necessary.

I was trying to figure out what you were trying to do with method 2, and I could not. But just because, here's a working version that does not use for each.

Sub format_worksheets() For x = 1 To Sheets.Count With Sheets(x) .Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, TrailingMinusNumbers:=True End With Next x End Sub

Note that this will error if any sheet has no data in Column A. I also left out the select statements because it's best practice to avoid them if they are not necessary.

I was trying to figure out what you were trying to do with method 2, and I could not. But just because, here's a working version that does not use for each.

Sub format_worksheets() dim x as Integer For x = 1 To Sheets.Count Sheets(x).Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, TrailingMinusNumbers:=True Next x End Sub

Note that this will error if any sheet has no data in Column A. I also left out the select statements because it's best practice to avoid them if they are not necessary.

Source Link
Daniel
Daniel
  • 13.1k
  • 3
  • 39
  • 64

I was trying to figure out what you were trying to do with method 2, and I could not. But just because, here's a working version that does not use for each.

Sub format_worksheets() For x = 1 To Sheets.Count With Sheets(x) .Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True, TrailingMinusNumbers:=True End With Next x End Sub

Note that this will error if any sheet has no data in Column A. I also left out the select statements because it's best practice to avoid them if they are not necessary.