That's simple enough to do.(I know, if it's so simply, why isn't it obvious?) to do JUST what you want in your example, you could use:Sub Make_Sheets() Dim rSource, rDest rDest = 5 Sheets("Sheet1").Range("B3").Value = Sheets("data").Range("F2").Value For rSource = 2 To 9 rDest = rDest + 1 Sheets("Sheet1").Cells(rDest, "A").Value = Sheets("data").Cells(rSource, "A").Value Sheets("Sheet1").Cells(rDest, "B").Value = Sheets("data").Cells(rSource, "B").Value Next rSource End Sub
but there are lots of things you need to do to make it "flexible". For instance:Name the worksheet after the teacher's name.(Have the macro copy the blank template for new teachers.)then, only move the students with that name.(that way, if only 7 students are listed, so be it). I made up a dummy file with 50 teachers and wrote this macro.(I also wrote a "clear_Teachers" macro to remove the test sheets.)see attached. Option Explicit Sub Make_Sheets() Dim Teacher, nRows Dim RowSource, RowDest Application.ScreenUpdating = False On Error Resume Next nRows = Application.WorksheetFunction.CountA(Sheets("data").Range("A:A")) For RowSource = 2 To nRows Teacher = Sheets("data").Cells(RowSource, "F").Value Err.Clear Sheets(Teacher).Select If (Err.Number <> 0) Then Sheets("Template").Copy After:=Sheets(Sheets.Count) ActiveSheet.Name = Teacher Sheets(Teacher).Range("B3").Value = Teacher End If RowDest = Application.WorksheetFunction.CountA(Sheets(Teacher).Range("A6:A16")) + 6 If (RowDest > 15) Then MsgBox "Students for Teacher: " & Chr(13) & Chr(13) & Teacher & Chr(13) & Chr(13) & " Exceeds 15" Else Sheets(Teacher).Cells(RowDest, "A").Value = Sheets("data").Cells(RowSource, "A").Value Sheets(Teacher).Cells(RowDest, "B").Value = Sheets("data").Cells(RowSource, "B").Value End If Next RowSource Sheets("data").Select Application.ScreenUpdating = True On Error GoTo 0 MsgBox "Finished: " & Sheets.Count & " Teachers" End Sub Paul----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- From: N .williams <nyree.willi...@gmail.com> To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> Sent: Wednesday, September 2, 2015 10:08 PM Subject: $$Excel-Macros$$ macro to loop through data sheet , fetch 8 rows at a time and put in new sheet Hello I've searched the forums and I can't find exactly what I need. I would like to know how to create new sheets based on a summary sheet. Copy students names: from columns A and B in data sheet to columns A and B in destination sheet1 Copy teacher's name: from column F in data sheet to cell B3 in destination sheet Fetch 8 rows (rows 2 - 9) then create new sheet and repeat for rows 10-17 Any help would be greatly appreciated! Thanks Nyree-- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout. -- Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @ https://www.facebook.com/discussexcel FORUM RULES 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Jobs posting is not allowed. 6) Sharing copyrighted material and their links is not allowed. NOTE : Don't ever post confidential data in a workbook. Forum owners and members are not responsible for any loss. --- You received this message because you are subscribed to the Google Groups "MS EXCEL AND VBA MACROS" group. To unsubscribe from this group and stop receiving emails from it, send an email to excel-macros+unsubscr...@googlegroups.com. To post to this group, send email to excel-macros@googlegroups.com. Visit this group at http://groups.google.com/group/excel-macros. For more options, visit https://groups.google.com/d/optout.
example.xlsb
Description: application/vnd.ms-excel.sheet.binary.macroenabled.12