adding text to end of cell

  • Thread starter Thread starter bubba1965
  • Start date Start date
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
 
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
 
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
 
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
 
Back
Top