Format results of Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
Back
Top