Transfer between excel worksheets Closed

Question

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.

Configuration: Windows 7 / Chrome 51.0.2704.103

vcoolio · Answer

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.

Transfer between excel worksheets

1 response

Similar discussions

Subscribe To Our Newsletter!