I have some data for my students in the range D2:G251, where each row is a different student.
I need to write a macro that, if there is an 'x' against their name in column D, will copy the data in columns E-G into range A1:B500 and copy that range into a new workbook and save the workbook with the students name.
The following code does this for the first row, but I don't know how to write a loop (or whatever) to make it do it for the other 249 rows.
Private Sub CreateNewSheets_Click() Dim ws1 As Worksheet Set ws1 = ThisWorkbook.Sheets("Template") Dim Rng1 As Range Set Rng1 = ws1.Range("A1:B504") varPath = ThisWorkbook.Path If ws1.Range("D2") = "x" Then ws1.Activate ws1.Range("e2:g2").Copy ws1.Range("B1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True Rng1.Copy Workbooks.Add ActiveSheet.Range("A1").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False ActiveSheet.SaveAs varPath & "\Student Data Files\" & ActiveSheet.Range("B1") & ".xls" ActiveWorkbook.Close Else End If End Sub
Any help you could give would be greatly appreciated.