Hi Howard,

Am Mon, 26 Oct 2015 03:03:04 -0700 (PDT) schrieb L. Howard:

Is the main problem with my initial code getting the AR name to become a sheet name is that AR is Dimmed as a Range and the sheet name must be a string?

the main problem is the loop through the cells:
For each Ar in Columns("A")
goes to each cell and the second loop gives already a wrong name and a
wrong range.

Your code revised:

Sub Staff_Info_To_Staff_Sheet()
Dim Ar As Range
Dim ArRow As Long, ArDwn As Long, LRow As Long
Dim myFirst As Long, myLast As Long, i As Long
Dim ArCpy As Range
Dim sNme As String

With Sheets("Main")
LRow = .Cells(Rows.Count, 2).End(xlUp).Row
myFirst = 1
For i = myFirst To LRow
sNme = .Cells(myFirst, 1)
If Len(.Cells(i, 2)) = 0 Then
Set ArCpy = .Range(.Cells(myFirst + 1, 2), .Cells(i - 1, 5))
Sheets(sNme).Cells(Rows.Count, 1).End(xlUp)(2) _
.Resize(ArCpy.Rows.Count, 4).Value = ArCpy.Value
myFirst = i + 1
End If
Next
End With

End Sub

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional