Workbook formatted as a 20 sheet record of pupils present in a 4 week cycle (Monday to Friday). First worksheet is Mon1 = Monday week 1, next is Tues1,etc. After Fri 1 worksheet moves into second week, ie Mon2. Macro analyses if the word absent is present in column E and copies the details contained in relative cells A8, B8, C8, D8, F8, G8 to next day (worksheet) starting the next days list at row 8.
I have spent a few hours looking through VBA tutorials as I am a complete beginner and am trying to cobble something together with my limited knowledge. I have written a few snippets manually which took me ages but it still returns an error . I could not find an example of how to copy specific cells in a row (hence I placed EntireRow.Copy in the macro).
Any advice or good websites to troll through (or even books) would be greatly welcomed:
Sub DetectAbsence()
'
' DetectAbsence Macro
' Macro recorded 30/09/2006 by
'
'
Dim mycell
Dim rng As Range
Set rng = Range("E8:E100")
For Each mycell In rng
If mycell.Value = "Absent" Then
mycell.EntireRow.Copy
Sheets("Tues1").Select
Range("A8").Select
Selection.End(xlDown)(2).Select
ActiveSheet.Paste
Next rng
End If
End Sub
This results in a compile error: Next without For.
I have spent a few hours looking through VBA tutorials as I am a complete beginner and am trying to cobble something together with my limited knowledge. I have written a few snippets manually which took me ages but it still returns an error . I could not find an example of how to copy specific cells in a row (hence I placed EntireRow.Copy in the macro).
Any advice or good websites to troll through (or even books) would be greatly welcomed:
Sub DetectAbsence()
'
' DetectAbsence Macro
' Macro recorded 30/09/2006 by
'
'
Dim mycell
Dim rng As Range
Set rng = Range("E8:E100")
For Each mycell In rng
If mycell.Value = "Absent" Then
mycell.EntireRow.Copy
Sheets("Tues1").Select
Range("A8").Select
Selection.End(xlDown)(2).Select
ActiveSheet.Paste
Next rng
End If
End Sub
This results in a compile error: Next without For.