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
--
Thanks for the help,

Glenn

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

Glenn wrote:

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
--
Thanks for the help,

Glenn

--

Dave Peterson

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
--
Cheers,
Ryan


"Glenn" wrote:

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
--
Thanks for the help,

Glenn