Copy cells in a row to next worksheet

[PP]

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.

 

[Norie]

I'm not sure what the actual question is but I can see what's wrong with the code, the End If is in the wrong place and the Next is referring to rng not mycell

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").Range("A8").End(xlDown)(2)
        End If
    Next mycell

End Sub

 

[PP]

Sorry I didn't make myself clear. This spreadsheet will track when people are absent on a given day and if they are it will copy their details onto the next day's roster held in the next tab. So if you are absent on Mon1 (week one Monday) it will transfer this person's details to the next day's roster (worksheet Tues1), starting at row 8.

The information about whether they are present or absent is entered in column E as either present or absent. Each person's details are held on the same row starting in row 8: columns A - I, excluding column E hold their personal details, which must be transferred to the next day's roster.

My question is how best to achieve a workable solution as to creating the next day's roster list starting with those who were absent on the preceeding day?

 

[PP]

Hope this has helped to clarify things.

 

[HalfAce]

Hello PP, welcome to the board.
If I understand what you're looking for then perhaps someting like this will help.

Option Explicit
Option Compare Text

Sub DetectAbsence()
Dim AbsntRng As Range, c As Range, DstRw As Long, LstRw As Long
Set AbsntRng = Range(Cells(8, "E"), Cells(Rows.Count, "E").End(xlUp))
If ActiveSheet.Index = Sheets.Count Then
  MsgBox "You are on the last sheet in this workbook." & Chr(10) & _
         "You will need to insert one or more sheets to continue."
         Exit Sub
End If
For Each c In AbsntRng
  If ActiveSheet.Next.Range("A8") = "" Then
    DstRw = 8
  Else
    DstRw = ActiveSheet.Next.Cells(Rows.Count, "A").End(xlUp)(2).Row
  End If
  If c.Value = "Absent" Then _
    Range(Cells(c.Row, "A"), Cells(c.Row, "I")).Copy ActiveSheet.Next.Cells(DstRw, "A")
Next c
LstRw = ActiveSheet.Next.Cells(Rows.Count, "E").End(xlUp).Row
Range(ActiveSheet.Next.Cells(8, "E"), ActiveSheet.Next.Cells(LstRw, "E")).ClearContents
End Sub

 

[PP]

Many thanks it works great. I think you are misnamed - it should be fullace!

Is the following possible within the detectabsence macro or will another macro need to be created Halface?

In column J there is a 'detentions remaining' box in which the number of detentions that a particular pupil must still do are inputted. If, for example, the number of detentions remaining = 3 then the pupil's details in the same row (excluding column E) are duplicated in the next two worksheets (the current + next 2 worksheets equalling 3). If present is entered into column E the detentions remaining value is reduced by 1. The macro should check that it has not 'double booked' the pupil into the same detention twice and move him to the next sheet without his / her name and form present(found in columns A,B & C).

I know this is really complex so please let me know if you need anything explained further.

 

[HalfAce]

Hello PP,
Thank you for the kind words.
Sure, that sounds easy enough to include into the same routine.
I thought I pretty much understood up until this statement:

The macro should check that it has not 'double booked' the pupil into the same detention twice and move him to the next sheet without his / her name and form present(found in columns A,B & C).

I can get the part about no duplicate entries and 3 more detentions going 1 each for the next 3 sheets but the rest of it ("without his / her name and form present(found in columns A,B & C") has me scratching my head a bit.

Can you elaborate some on that?

 

[PP]

Sorry about that. Duplicate pupil entries will be identified by their name and form (the christian name in column A, surname in column B and form code in column C).

Theoretically, a pupil may pick up quite a lot of consecutive detentions for trunating - for each school session (lesson) they truant they get 2 detentions, which means their name is entered into the current sheet and the next day's sheet. If they truant a whole school day their name would appear on 10 sheets.

Let's say for example the same student who has 10 detentions (Mon-Fri for 2 weeks!) comes late on the first Monday they are supposed to be in detention for truanting (1 late = 1 detention). Their details would again be entered into the Monday sheet but they already appear on this list for truancy. This pupil's late detention must be moved to the first sheet that they don't appear on (ie 10 sheets down).

Would emailing you a copy of the spreadsheet makes things easier so you can see how it is organised or will the screenshot below suffice? I have highlighted in red "Leanne Brown" to show that she has been entered onto the same day's roster twice and needs to be moved to the next free sheet that does not have her name & form on it.

Improving Lates Systems.xls
	A	B	C	D	E	F	G	H	I	J
1
2			Lates & Truancy Management System
3
4
5					Register	Reason:	Lates	Truancy
6					absent	Late	am	No. of		Detentions
7	Christian Name	Surname	Form	Date	present	Truancy	pm	sessions	Special Circumstances	remaining
8	Joe	Bloggs	11KG	23.11.06	absent	Late	am			1
9	Martin	Smith	11LN	22.11.06	present	Late	am			1
10	Leanne	Brown	11PQ	23.11.06	absent	Truancy		5		10
11	Marie	White	11LN	22.11.06	present	Late	pm			0
12	Leanne	Brown	11PQ	23.11.06		Late	am			11
2.10.06

 

[PP]

Hope this screenshot clears things up.

 

[PP]

Hopefully Halface can respond when he is not busy