Macro for renaming sheets

**Joeg101** · 02-10-2008, 04:14 PM

Thank you all for your help in the past. My skill level is pretty low so I greatly appreciate it.
Here’s my situation that I need help on:
I have a sheet with 200 names on it organized into columns by last name, first name, category, etc. I need to provide a sheet for each name. Basically, a workbook with 200 sheets and the name of each sheet is equal to the last name.
Is there a macro that I can run that
(1) will first “take row 2 of Sheet1 and paste it to row 2 of sheet 2”
(2) then “it will change the name of ‘Sheet2’ to equal the person’s last name located in ‘A2’ of the same sheet (Sheet2)”
(3) and finally it will “repeat the process on Sheet3; copying row 3 of Sheet1 to row 2 of Sheet3 and then renaming Sheet3 to equal the person’s last name located in ‘A2’ of the same sheet (Sheet3).
It obviously does not have to be performed by only one macro. Any advice would be greatly appreciated.
-Joe

**VBA Noob** · 02-10-2008, 04:33 PM

Maybe

Sub Macro1()
Dim Lr As Long
Dim NWsht As Worksheet
Dim SrcSht As Worksheet
Set SrcSht = Sheets("Sheet1") ' Change to your name
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
    For i = 2 To Lr
        Set NWsht = Sheets.Add
        NWsht.Move After:=Sheets(Sheets.Count)
    
        NWsht.Rows(2).Value = SrcSht.Rows(i).Value
        NWsht.Name = NWsht.Range("A2").Value
    Next i
Application.ScreenUpdating = True
End Sub

VBA Noob

**VBA Noob** · 02-10-2008, 05:08 PM

Some error checking

Sub Macro1()
Dim Lr As Long, M As Long
Dim NWsht As Worksheet
Dim SrcSht As Worksheet
Set SrcSht = Sheets("Sheet1") ' Change to your name
Lr = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
On Error Resume Next
    For i = 2 To Lr
        Set NWsht = Sheets.Add
        NWsht.Move After:=Sheets(Sheets.Count)
        
        NWsht.Rows(2).Value = SrcSht.Rows(i).Value
             NWsht.Name = NWsht.Range("A2").Value
         If Err.Number <> 0 Then
            MsgBox Err.Number & " " & Err.Description
            Err.Clear
            M = MsgBox("Stop Macro!!", vbYesNo)
            If M = vbYes Then
                Exit For
            End If
         End If
       
    Next i
Application.ScreenUpdating = True
End Sub

VBA Noob

**Joeg101** · 02-10-2008, 05:27 PM

Absolutely Amazing! Thank you so much. You don’t understand how much time and aggravation you just saved me…actually, you probably do.

One more question; On the line :