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
Last edited by nyree; 09-02-2015 at 09:24 PM.
A few questions.
summary sheet? Is that Sheet "data" or "sheet1" in your attachment?
After it has been copied to the end, what name will it need to be named?
The following means nothing to me at all
"Fetch 8 rows (rows 2 - 9) then create new sheet and repeat for rows 10-17"
Explain that so we can understand it please.
Hello Jolivanes
Thanks for replying
sorry I wasn't clear - yes summary sheet = data
- Cells A2 - A9 on the data sheet: copied to sheet 1 column A
- Cells B2 -B9 on the data sheet: copied to sheet1 column B
- Cells F2 - F9 on the data sheet: copied to cell B3 on sheet1
- The new sheet will be named from the contents of cell B3 on sheet1
Loop the steps above for the next 8 rows in the data sheet to the end
I hope this is clearer now?
Apologies again for the confusion
Nyree
Let us know what you want changed in the attached
Could not attach a workbook so this is the code I used.
Please Login or Register to view this content.
Hello Jolivanes
Thanks for your help - I have a solution which was given to me from another site - its exactly what I needed. I'm posting it below in case its useful to others:
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
Regards
Nyree
Nyree,
Your thread is in violation of Rule 3 Use Code Tags Around Code and Rule 8 Don't cross-post without a link.
This a classic reason why Rule 8 was introduced in that jolivanes has effectively wasted their time trying a provide you with a solution when someone else had already done so. Please remind yourself of the forum rules here.
Robert
Last edited by Trebor76; 09-04-2015 at 02:16 AM.
____________________________________________
Please ensure you mark your thread as Solved once it is. Click here to see how
If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks