Change "With Worksheets" to "Activesheet"
 

Code works perfectly....

But plan on using this on various sheets.
How do i change this line to "active sheet"?

With Worksheets("sheet 1")



Sub GetFirstFourLetters()
Dim Col As Variant
Dim X As Long
Dim Z As Long
Dim LastRow As Long
With Worksheets("s.07")
For Each Col In Array("A", "B")
LastRow = .Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To Len(.Cells(X, Col).Value)
With .Cells(X, Col)
If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then
.Offset(0, 2).Value = Mid$(.Value, Z, 4)
Exit For
End If
End With
Next
Next
Next
End With
End Sub


Thanx

Change "With Worksheets" to "Activesheet"
 

With ActiveSheet
--
Gary''s Student - gsnu200796

Change "With Worksheets" to "Activesheet"
 

You said...

With Worksheets("sheet 1")

but I am guessing you meant...

With Worksheets("s.07")

which is what is in the code. Anyway, change it to this...

With ActiveSheet

ActiveSheet is a automatically tracked object in VB referencing the active
sheet in the workbook.

Rick


"J.W. Aldridge" wrote in message
...
Code works perfectly....

But plan on using this on various sheets.
How do i change this line to "active sheet"?

With Worksheets("sheet 1")



Sub GetFirstFourLetters()
Dim Col As Variant
Dim X As Long
Dim Z As Long
Dim LastRow As Long
With Worksheets("s.07")
For Each Col In Array("A", "B")
LastRow = .Cells(Rows.Count, Col).End(xlUp).Row
For X = 1 To LastRow
For Z = 1 To Len(.Cells(X, Col).Value)
With .Cells(X, Col)
If Mid$(.Value, Z, 1) Like "[A-Za-z]" Then
.Offset(0, 2).Value = Mid$(.Value, Z, 4)
Exit For
End If
End With
Next
Next
Next
End With
End Sub


Thanx