Change "With Worksheets" to "Activesheet"

J

J.W. Aldridge

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
 
R

Rick Rothstein \(MVP - VB\)

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top