For each loop does not loop

[Roaldb]

I have a procedure that calls other procedures from Personal workbook based on text string in cell A2.
It works perfect for the first sheet, but it wont loop the sheet. After the code runs for the first sht I get an "Application-defined or object-defined error.

what do I do wrong?


Sub TextToColumns()
Dim sht As Worksheet


For Each sht In ActiveWorkbook.Worksheets


If sht.Range("A2") Like "*NEW: NR255.P1BAIBKR.P190BEL.WRK03*" Then
Application.Run "Personal.xlsb!Felix310"


ElseIf sht.Range("A2").Value Like "*OLD: NR255.P1BAIBKR.P310BEL.MEL03*" Then
Application.Run "Personal.xlsb!KVH_310"


ElseIf sht.Range("A").Value Like "*OLD: NR255.P1BAIBKR.P190BEL.MEL02*" Then
Application.Run "Personal.xlsb!KVH_Felix"

End If

Next

End Sub

 

[steve the fish]

That will loop ok aside from one range saying "A" rather than "A2". It must be something to do with the called macro.

 

[Roaldb]

This is an example of one of the called macroes (made from macro recorder)
it will sort text in to clumns.

Range("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(1, 1), Array(4, 1), Array(18, 1), Array(30, 1)), _
TrailingMinusNumbers:=True
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Columns("D:D").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("B1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet2").Sort
.SetRange Range("A1:C96")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Selection.NumberFormat = "0"

ActiveSheet.Name = "KVH FELIX"

End Sub