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

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/general-topics-in-vba/create-worksheets-from-a-list-of-names-using-vba-in-microsoft-excel.html
SHARE




How to create a worksheet?

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

Answer:
We write the macro for loops in Excel VBA, do until loop we will use for this problem. 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
Please follow and like us:
0


One thought on “Create worksheets from a list of names using VBA in Microsoft Excel

  1. Pingback: van cleef alhambra necklace carnelian imitation

Leave a Reply

Your email address will not be published. Required fields are marked *

To avoid automated spam,Please enter the value *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>