Append Worksheet name to each begining of each row with values

[Glenn]

Excel 2007

Hello,

I need to create a macro that will go through each worksheet and append the
worksheet name to the begining of each row if there is a value. Here is what
I have so far. This runs but takes my last worksheet name and appends that
name to the rows in my first worksheet instead of appending my first
worksheet name.

Sub Append()

Dim LastRow As Long
Dim cl As Range
Dim ws As Worksheet

For I = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(I)
.Cells(I, 1).Value = ws.Name
End With
Next I

LastRow = Range(Cells(Rows.Count), Cells(Rows.Count, 1)).End(xlUp).Row

For Each cl In Range(Cells(1, 1), Cells(LastRow, 1))
If Not IsEmpty(cl) Then
cl.Value = ws.Name & "." & cl.Value
End If
Next cl

End Sub

 

[Dave Peterson]

First, I wouldn't use Append as the procedure name. VBA uses it in some file
statements.

Option Explicit
Sub myAppend()

Dim LastRow As Long
Dim i As Long
Dim cl As Range

For i = 1 To Worksheets.Count
With Worksheets(i)
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For Each cl In .Range("A1", .Cells(LastRow, "A")).Cells
If IsEmpty(cl) Then
'skip it
Else
cl.Value = .Name & "." & cl.Value
End If
Next cl
End With
Next i
End Sub

 

[Ryan H]

You were kinda close. This should do what you were asking. Hope this helps!
If so, let me know, click "YES" below.

Option Explicit

Sub Append()

Dim wks As Worksheet
Dim LastRow As Long
Dim cell As Range

For Each wks In Sheets
LastRow = wks.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In wks.Range("A1:A" & LastRow)
If Not IsEmpty(cell) Then
cell.Value = wks.Name & "." & cell.Value
End If
Next cell
Next wks

End Sub