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

benrabe

New Member
Joined
Sep 14, 2020
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
  2. Mobile
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:

VBA Code:
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,123,425
Messages
5,601,580
Members
414,460
Latest member
uctc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top