CODEWITHSUNDEEP
vbaexcel
Cielo Salas
Cielo Salas

Reputation: 95

vba Create additional tabs in the worksheet based on a list in a table

i am not well versed in vba and macros and i need help in creating additional worksheets based on a list. So i have a table with a class list on the first column, location on 2nd column, Trainer in column 8 and start date in column 11. I need to also rename the copied tab using the class list data and put that into cell E6 as well. Problem is i don't know how to also get the location, Trainer and start date for each class and put those in the copied tab (start date in E5, Trainer in E7 and location in E8.

Here is what i have so far:

   Sub CreateCATtabs()
        On Error GoTo GetOut

        Dim cName As Range, cList As Range

        Set cList = Sheets("Control").Range("ClassList2017")

        For Each cName In cList
            If cName.Value = "" Then GoTo GetOut

            Sheets("Class Attendance").Copy After:=Sheets(Sheets.Count)
            With ActiveSheet
               .Name = cName.Value
               .Range("E6").Value = cName.Value

            End With
        Next cName
        GetOut:

    End Sub

Upvotes: 1

Views: 337

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

I think this will work. I refactored a bit to avoid the GoTo statement and declare some explicit objects. Proper error trapping always beats GoTo statements.

Option Explicit

Sub CreateCATtabs()

    Dim wsControl As Worksheet, wsAttendance As Worksheet

    Set wsControl = Worksheets("Control")
    Set wsAttendance = Worksheets("Class Attendance")

    Dim cName As Range, cList As Range

    Set cList = wsControl.Range("ClassList2017")

    For Each cName In cList

        If cName.Value <> "" Then

            wsAttendance.Copy After:=Sheets(Sheets.Count)

            Dim wsCopy As Worksheet
            Set wsCopy = ActiveSheet

            With wsCopy

               .Name = cName.Value
               .Range("E6").Value = cName.Value
               .Range("E5").Value = cName.Offset(, 10).Value 'offset is 1 minus the column number
               .Range("E7").Value = cName.Offset(, 7).Value
               .Range("E8").Value = cName.Offset(, 1).Value

            End With


        Else

            Exit For

        End If

    Next cName

End Sub

Upvotes: 2

Related Questions