Transfer between excel worksheets

[Closed]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello,

I would like to be able to transfer an entire row of attendance of students when it hits zero for attendance to another sheet or at the bottom of the same sheet. I have tried the if statement but not getting any where.

I appreciate all you assistance. I did looked at that website. I am working on attendance for students. They are given X number of day to complete their days of attendance. Once they have completed the attendance their number of days is zero. What I would like to do is if the students is at zero say in B7. I would like to see if the entire student cell (row) can be moved to another sheet and be added to the rest. I need to keep a record of those students for future reference. My worksheet already counts the number of absence and present for each student.

Thank you for your asssistance.

1 reply

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Vira,

Perhaps the following code may do the task for you:-

Sub Transfer()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    With Range("B1", Range("B" & Rows.Count).End(xlUp))
        .AutoFilter 1, 0#
        On Error Resume Next
        .Offset(1).EntireRow.Copy
        Sheet2.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteValues
        .Offset(1).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select

End Sub


Following is the link to my test work book for you to peruse. Click on the "RUN" button to see it work.

https://www.dropbox.com/s/9wc4n3kdeboa20j/Vira%28transfer%20to%20sht2%2Cautofilter%29.xlsm?dl=0

The code filters Column B for the numerical value zero (0) and then transfers the relevant row of data to sheet2. The relevant entry from sheet1 (your input) sheet is deleted.

Try the code in a sample of your work book first.

I hope that this helps.

Cheerio,
vcoolio.