I have a workbook with a sheet for each client; I'm trying to create an Index
sheet. I've got a code for returning a value from each sheet, but would like
to only have it return the 1st 3 characters from that cell. The Excel
function is LEFT(text, num_chars). For example, the macro might find a value
ABC-06-78; I only want the ABC. The code I'm using to create the index itself
is:

Sub CreateIndex()

Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
With Sheets(1)
.Range("A2").Value = "Client Code"
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Range("B5")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Help!

You can try this. You had the first iteration overwriting your "Client Code"
header so I chaned i to = 3. The Left function seems to work ok.

"jcmcknight" wrote:

I have a workbook with a sheet for each client; I'm trying to create an Index
sheet. I've got a code for returning a value from each sheet, but would like
to only have it return the 1st 3 characters from that cell. The Excel
function is LEFT(text, num_chars). For example, the macro might find a value
ABC-06-78; I only want the ABC. The code I'm using to create the index itself
is:

Sub CreateIndex()

Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
With Sheets(1)
.Range("A2").Value = "Client Code"
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Range("B5")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Help!

Sorry about that. I forgot to paste the code:

Sub CreateIndex()

Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
With Sheets(1)
.Range("A2").Value = "Client Code"
i = 3
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = Left(ws.Range("B5"), 3)
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub

"jcmcknight" wrote:

I have a workbook with a sheet for each client; I'm trying to create an Index
sheet. I've got a code for returning a value from each sheet, but would like
to only have it return the 1st 3 characters from that cell. The Excel
function is LEFT(text, num_chars). For example, the macro might find a value
ABC-06-78; I only want the ABC. The code I'm using to create the index itself
is:

Sub CreateIndex()

Dim ws As Worksheet
Dim i As Integer

Application.ScreenUpdating = False
Sheets(1).Activate
With Sheets(1)
.Range("A2").Value = "Client Code"
i = 2
For Each ws In ThisWorkbook.Worksheets
If ws.Index < 1 Then
.Rows(i).Cells(1).Value = ws.Range("B5")
i = i + 1
End If
Next
End With
Application.ScreenUpdating = True
End Sub


Help!