link

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I tried this yesterday, but no reply. Let's give it one
more go...I am trying to set up an archive spreadsheet
that removes a row from a source spreadsheet when a
certain cell has been filled in. For example, in a single
row on the source spreadsheet I have information on one
product. When I fill in the last cell in the row with a
shipped date, I want that row to move from the active
inventory (source spreadsheet) to the archive inventory
(dependent spreadsheet). Is it possible?

Thanks in advance for any suggestions.

-E. Wagner
 
One way:

Assume 10 columns of data.

Put this in the Worksheet code module (right click the worksheet tab and
choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If .Column = 10 Then
With Cells(.Row, 1).Resize(1, 10)
.Copy Worksheets("archive").Range( _
"A" & Rows.Count).End(xlUp).Offset(1, 0)
.EntireRow.Delete
End With
End If
End With
End Sub
 
Back
Top