Why dont you name your worksheet sonce your loop is completed. use
this bit of code. Must change cell references.

Dim n As Integer
Dim wa As Worksheet
For Each wa In ActiveWorkbook.Worksheets
wa.Activate
On Error Resume Next
wa.Name = Range("a2").Value
Next



(Repoman) wrote in message . com...
Hello Excel Experts,

I'm looking for a macro that can copy range A2:G2 in the worksheet
"setup" to a new worksheet, name the new worksheet according to the
value in cell A2, and then repeat the procedure for the next row
(A3:G3). The macro should continue to run until all rows with data in
"setup" have been copied to new sheets. "Nice to have" but not
necessary would be a step to copy the header row of "setup" A1:G1 to
each of the new sheets at the top.

I found macros in this group to copy the range and create a worksheet,
and a looping macro to create new worksheets based on a list, but I
can't figure out how to combine the two. I've posted them below. (is
it proper netiquette to name the people who posted them originally?)

Any help would be greatly appreciated. Thanks, E. Kohl


Sub CopyRange()
Dim ws As Worksheet, ws1 As Worksheet, c As Range
Set ws = ActiveSheet
Set c = ActiveCell
Set ws1 = Sheets.Add
ws.Range("A2:G2").Copy ws1.Range("A1")
ws1.Name = ws1.Range("A1")
ws.Select
c.Select
End Sub

Sub nameSheetsFromSheet()
Dim vNames() As Variant
Dim Cntr As Long
Dim wbNew As Worksheet
vNames() = Worksheets("Setup").Range("A2:A" & Worksheets("Setup") _
.Range("B65536").End(xlUp).Row).Value
For Cntr = 1 To UBound(vNames())
Set wbNew = ThisWorkbook.Worksheets.Add
wbNew.Name = vNames(Cntr, 1)
Set wbNew = Nothing
Next Cntr
End Sub