Moving Data between excel Sheets

Question

I have to create health reports for daily surveillance on buses. I also have to make sure that I have done health checks on all the buses at least once before the end of the quarter. I will give a brief description of what I have to do.

1> Every day 10-15 health queries comes in for random bus number for which I have to do a health report daily.

2> Before the end of the quarter I have to do health checks for all the buses and report it.

My question:

I have a master list of total of 800 buses which I want to put in sheet 3 for example. I want to create a field where I enter a particular bus number for which I do health check and it should go in Sheet 1 called: Completed and the remaining not completed buses numbers should go in sheet 2 called: Not Completed. This way I don't have to redo the reporting for the random queries at the end of the quarter. So every day I keep entering random bus number in that field and it should remove those buses from Not Completed sheet and add it to Completed sheet.

Is there a particular way to do it?

Answer 1

It'd be easiest to just add a button, and in the vba code for the button move the row you need to move from Sheet 2 to Sheet 3, then delete the row in question on Sheet 2.

The code for that button:

Private Sub Complete_Click()

  Dim FoundRow As Integer
  Dim LastRow As Integer

  FoundRow = Sheets("Sheet2").Range("A:A").Find(Sheets("Sheet2").Range("C1"), _
             Sheets("Sheet2").Range("A2"), xlValues).Row
  LastRow = Sheets("Sheet1").UsedRange.Rows.Count+1

  Sheets("Sheet2").Range("A:A").Rows(FoundRow).EntireRow.Copy
  Sheets("Sheet1").Range("A:A").Rows(LastRow).PasteSpecial (xlValues)
  Sheets("Sheet2").Range("A:A").Rows(FoundRow).EntireRow.Delete (xlShiftUp)

End Sub

I'd then have a button on Sheet 3 that would reset all the Sheets.

Here's some simple code for the button for Sheet 3 (though I would put a safety Message box in):

Private Sub ResetSheet_Click()

  Worksheets("Sheet1").UsedRange.ClearContents
  Worksheets("Sheet2").UsedRange.ClearContents

  Worksheets("Sheet3").UsedRange.Copy
  Worksheets("Sheet2").Range("A2").PasteSpecial (xlPasteValues)

End Sub