Loop until last worksheet

Question

I am trying to apply a format to every worksheet in my current workbook. I have tried "For Each" and I have tried to Loop through until I reach last worksheet but both error due to different reasons. Can someone please tell me what I'm doing wrong?

Method 1: It works on the 1st worksheet but not on the remaining worksheets.

Sub format_worksheets() Dim ws As Worksheet For Each ws In Worksheets Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=False, Comma:=True, Space:=False, Other:=True,TrailingMinusNumbers:=True Range("A1").Select Next ws End Sub

Method 2: It doesn't recognise the last worksheet.

Sub format_worksheets() Dim ws As Worksheet ws = Worksheet.Active Do code Loop Until ws = Sheets(Sheets.Count).Active End Sub

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-09-11 16:00:34Z

4

In method one, try changing:

Columns("A:A").Select

To

ws.Activate ws.Columns("A:A").Select

... to activate the worksheet and specify the worksheet you want to work with.

edited Sep 11, 2012 at 16:00

answered Sep 11, 2012 at 15:55

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1624926 Over a year ago

That worked - thank you very much. I can't believe it was that simple. Appreciate your input.

user1624926 Over a year ago

Thanks again - appreciate the help.

Reafidy Over a year ago

You should mark the question as solved by clicking the green tick. This prevents other developers (like my self) opening your questions thinking you still need help.

Daniel · Accepted Answer · 2012-09-11 17:13:35Z

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.

Collectives™ on Stack Overflow

Loop until last worksheet

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related