move row to next sheet

[joemeshuggah]

does anyone have code that after the value of a cell in a particular column
for the active row is changed, the row is deleted and appended to another
worksheet in the workbook?

 

[Gord Dibben]

Sheet event code.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False
If Target.Address = "$A$1" And Target.Value <> "" Then
With Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
.Offset(1, 0).EntireRow = Target.EntireRow.Value
End With
Target.EntireRow.Delete
End If
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[joemeshuggah]

this works quite nice...thanks! is there anyway to maintain the formatting
from the originating sheet?

 

[Gord Dibben]

Yes, it can be done but I'm trying to visualize what you're doing.

If you copy the target row to Sheet2 with values and formats then delete the
target row, your data and formats for that row are gone.

The row that was below now becomes target row.

Does it have data and formats also?

Or maybe you just want original target row cleared of contents only, leaving
a blank row with formats?


Gord

 

[joemeshuggah]

i believe i figured out what i needed...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo stoppit
Application.EnableEvents = False



If Target.Column = 5 And Target.Row > 2 And Target.Value <> "" Then
With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp)
.Offset(1, 0).EntireRow = Target.EntireRow.Value
End With

Target.EntireRow.Delete

With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Resize(1, 5).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With


With Sheets("Account-Specfic Explanations").Cells(Rows.Count,
"A").End(xlUp).Offset(0, 4).WrapText = True
End With


End If
stoppit:
Application.EnableEvents = True
End Sub

thanks!