Moving Rows to another sheet in a workbook

Question

I need Help! I am not well versed in VBA or Macros but i cannot find any other way to accomplish what i need to do without using it.

I have a sheet which i will be using to track Purchase orders, and what i need to do is; when i have a row in sheet 1 (Purchase Orders) which has been recieved i.e. the date of receipt has been recorded in column H i need for the entire row to be cut and pasted into sheet 2 (Received orders).

The header takes up the first 7 rows the rows, so i need the macro to look at rows 8-54. Once the received items are removed from sheet 1, i need the row to also be deleted or preferably for the list to be sorted by column A moving the now empty row which has been cut from open for a future entry.

Any help would be greatly appreciated.

Answer 1

I tested this out, here's one way to do it:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Dim receivedDate As Range, nextOpen As Range, isect As Range

Set receivedDate = Sheet1.Range("H8:H54")
Set isect = Application.Intersect(Target, receivedDate)

If Not (isect Is Nothing) And IsDate(Target) = True Then
    Set nextOpen = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
    Target.EntireRow.Copy Destination:=nextOpen.EntireRow
    Target.EntireRow.Delete
End If

Application.EnableEvents = True

End Sub

This would be pasted into the Sheet1 code. Any time a cell is changed on sheet1, the code checks to see if it's in the critical range that you specified. (H8:H54) If it is, it then checks to see if it's a date. If it is, it then copies the entire row, puts it in the next open row on Sheet2, and deletes the original row. The cells below it will get shifted up so there are no gaps.

Since the code functions on a cell changing event, it disables "Application.EnableEvents" in order to avoid a loop of changing a cell to call an event which changes a cell to call an event... etc.

Answer 2

The "Record Macro" feature should be enough to do the task you describe.. In Excel 2007, go to the Developer tab in the Ribbon, and select "Record Macro", and perform exactly the steps you are describing. It will record the equivalent VBA code, which you can then execute - or tweak/modify.