Copying data from rows in a specific column based on a sheet name

[benrabe]

Hi, i have some code, i managed to get a macro to create new sheets as lesson plans from a template lesson.

My work book has one visible sheet and one hidden. The lesson list is the sheet where i will enter my curriculum, dividing each lesson into a column. The lesson plan will have name which will appear in row two. I also use this details to label my newly created lesson plan sheets.

My next step is to copy data from the lesson list and paste it into the relevant sheet and cells.

I think i will have to use a second range checker but i am not sure - please see my current script posted below:

Sub Add_New_Lesson()

Call Copy_Lesson_Template(Sheets("Lesson List").Range("B2:XFD2"))


End Sub


Sub Copy_Lesson_Template(Names_Of_Sheets As Range)

Dim No_Of_Sheets_to_be_Added As Integer
Dim Sheet_Name As String
Dim i As Integer

Worksheets("Lesson Template").Visible = xlSheetVisible

No_Of_Sheets_to_be_Added = Names_Of_Sheets.Columns.Count

For i = 1 To No_Of_Sheets_to_be_Added
Sheet_Name = Names_Of_Sheets.Cells(1, i).Value


If (Sheet_Exists(Sheet_Name) = False) And (Sheet_Name <> "") Then
Worksheets("Lesson Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = (Sheet_Name)

Call Copy_Lesson_Details

End If

Next i

Worksheets("Lesson Template").Visible = xlSheetHidden

End Sub


Function Sheet_Exists(WorkSheet_Name As String) As Boolean
Dim Work_sheet As Worksheet

Sheet_Exists = False

For Each Work_sheet In ThisWorkbook.Worksheets

    If Work_sheet.Name = WorkSheet_Name Then
        Sheet_Exists = True
    End If

Next

End Function

Sub Copy_Lesson_Details(Names_Of_Sheets As Range)

Dim No_Of_Sheets_to_be_Added As Integer
Dim Sheet_Name As String
Dim i As Integer








Sheets("Lesson List").Select
Range("B3").Copy
Sheets(Sheets.Count).Select
Range("A2").Select
ActiveSheet.Paste



End Sub