Copy cells in a row to next worksheet

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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

Board Regular
Joined
Sep 30, 2006
Messages
139
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?
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

Hello PP, welcome to the board.
If I understand what you're looking for then perhaps someting like this will help.
Code:
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

Board Regular
Joined
Sep 30, 2006
Messages
139
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454

ADVERTISEMENT

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

Board Regular
Joined
Sep 30, 2006
Messages
139
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
ABCDEFGHIJ
1
2Lates & Truancy Management System
3
4
5RegisterReason:LatesTruancy
6absentLateamNo. ofDetentions
7Christian NameSurnameFormDatepresentTruancypmsessionsSpecial Circumstancesremaining
8JoeBloggs11KG23.11.06absentLateam1
9MartinSmith11LN22.11.06presentLateam1
10LeanneBrown11PQ23.11.06absentTruancy510
11MarieWhite11LN22.11.06presentLatepm0
12LeanneBrown11PQ23.11.06Lateam11
2.10.06
 

PP

Board Regular
Joined
Sep 30, 2006
Messages
139
Hopefully Halface can respond when he is not busy :)
 

Forum statistics

Threads
1,136,305
Messages
5,674,984
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top