Copy to sheet name in column 1
Trying to copy AREAs to the sheet of the employee whose name is the same as "Ar" in the - For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
If current Ar = Name 1 then the AREA should go to the sheet named Name 1.
I'm using Name 1, Name 2 etc, as sheet names and employee names.
"Main" sheet column 1 has Name n and data for Name n is in columns B to E down any number of rows. Say 5 to 45, varies.
There is a one blank row between AREAs.
The "Set ArCpy As..." works just fine, can't get the copy-to-sheet-name thing correct.
Thanks,
Howard
Sub Staff_Info_To_Staff_Sheet()
Dim Ar As Range
Dim ArRow As Long, ArDwn As Long
Dim ArCpy As Range
Dim sNme As Worksheet
For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
ArDwn = Ar.Offset(, 1).End(xlDown).Row
'Set sNme.Name = Ar.Offset(0, 0)
Set ArCpy = Range(Cells(Ar.Row, 2), Cells(ArDwn, 5))
ArCpy.Copy Sheets(sNme).Range("A" & Rows.Count).End(xlUp)(2)
Next
End Sub
|