Hello Paul
What can I say? Its perfect - exactly what I needed -
THANK YOU SO MUCH
ps it may seem easy for you!
Thanks again for your help, I'm so grateful.
Best regards
Nyree
On 03-Sep-15 2:34 PM, Paul Schreiner wrote:
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
<mailto:excel-macros+unsubscr...@googlegroups.com>.
To post to this group, send email to excel-macros@googlegroups.com
<mailto: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 a topic in the
Google Groups "MS EXCEL AND VBA MACROS" group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/excel-macros/mgdrAYWRH3o/unsubscribe.
To unsubscribe from this group and all its topics, send an email to
excel-macros+unsubscr...@googlegroups.com
<mailto:excel-macros+unsubscr...@googlegroups.com>.
To post to this group, send email to excel-macros@googlegroups.com
<mailto: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.
