» Create worksheets from a list of names using VBA in Microsoft Excel

Question:
For each name, a worksheet is to be created. The respective data is to be copied in the specific sheet.


Answer:
Place the code below into the standard module.

Sub AfterNamesCopying()

   Dim wks As Worksheet, wksData As Worksheet

   Dim intRow As Integer, intRowL As Integer

   Dim strSheet As String

   Application.ScreenUpdating = False

   Set wksData = ActiveSheet

   intRow = 1

   On Error Resume Next

   Do Until IsEmpty(wksData.Cells(intRow, 1))

      If Left(wksData.Cells(intRow, 1), 4) = "name" Then

         Set wks = Worksheets(wksData.Cells(intRow, 1).Value)

         If Err > 0 Or wks Is Nothing Then

            Err.Clear

            Worksheets.Add after:=Worksheets(Worksheets.Count)

            Range("A1") = "Data"

            ActiveSheet.Name = wksData.Cells(intRow, 1)

         End If

      End If

      intRow = intRow + 1

   Loop

   On Error GoTo 0

   Worksheets(2).Select

   intRow = 1

   Do Until IsEmpty(Cells(intRow, 1))

      If Left(Cells(intRow, 1), 4) = "name" Then

         strSheet = Cells(intRow, 1)

      Else

         With Worksheets(strSheet)

            intRowL = .Cells(Rows.Count, 1).End(xlUp).Row + 1

            .Cells(intRowL, 1).Value = Cells(intRow, 1).Value

         End With

      End If

      intRow = intRow + 1

   Loop

   Application.ScreenUpdating = True

End Sub