adding text to end of cell

B

bubba1965

I posted this at the miscellaneouos forum but got no response.

I am wondering if someone can help me with a macro that would add
comma, a space and text at the end of every cell.

My workbook has about 40 sheets and the cells that I want to change ar
all in the same column (column A).

The text that I want to add is the name of the worksheet. So basically
I want to add to the end of every cell in column A - a comma - a spac
- and the name of the worksheet.

For example, the first cell in column A reads "Mar. 3". The name of th
worksheet is "1971" Running the macro would change this cell to Mar
3, 1971

Can this be done with a Macro? Any help would greatly be appreciated
Thank
 
F

Frank Kabel

Hi
try the following
Sub append()
Dim wks As Worksheet
Dim rng As Range
Dim cell As Range

For Each wks In Worksheets
Set rng = wks.Range("A1:A1000")
For Each cell In rng
With cell
If .Value <> "" Then
.Value = .Value & ", " & wks.Name
End If
End With
Next cell
Next wks
End Sub
 
T

Tom Ogilvy

Sub AppendSheetName()
Dim sh As Worksheet
Dim rng As Range
Dim cell As Range
For Each sh In ActiveWorkbook.Worksheets
Set rng = sh.Range(sh.Cells(1, 1), sh.Cells(Rows.Count, 1).End(xlUp))
For Each cell In rng
If Not cell.HasFormula Then
If Len(Trim(cell.Value)) > 0 Then
cell.Value = cell.Value & ", " & sh.Name
End If
End If
Next
Next
End Sub


Test on a copy of your workbook
 
B

bubba1965

Thanks,

Both of those worked great. I appreciate your help


Does anyone know why the email notificaton does not seem to work fo
me. I have check email notification and I am a registered user, yet
never receive a notification whenever I get a response? Just wonderin
what I am doing wrong
 
Top