Moving complete row from one sheet to another

  • Thread starter Thread starter Ultimate
  • Start date Start date
U

Ultimate

I would like to be able to have a complete row moved from my curren
sheet to my closed sheet by changing one value in one cell. I woul
like to be able to have this work in every cell of a sigle column. Jus
as a side note, the cell I would like to use is also set to validatio
from a list in my Data 1 sheet.

This is the best source for my needs so dar, thanks guys!!
 
I think that you'll need to supply a few more details to get useful responses.

I would open the secondary workbooks to add that row, then save and close it.

But I'm betting that each value in that column corresponds to a different
workbook. (and you'll have to say what worksheet in each secondary workbook
gets the data.) So any responder would have to know how you determine which
workbook and how to find them (all in the same folder???).

And just my personal opinion, I wouldn't make it so automatic. My typing would
mean that I made lots of mistakes that I'd have to correct.

I'd put a button from the forms toolbar on the worksheet that forced the user to
click on it when they were ready. (And move the row with the activecell).
 
The sheets are all contained in one workbook. The first sheet is m
current one, That is, it is the one in which I enter all of the dat
from each case that is sent to me, and have some sort of open/workin
status.
The second sheet contains the cases that I have closed in the firs
sheet.
When I change one column in the first sheet from any status (open
awaiting customer, pending shipment, etc.)to closed, I cut the row fro
the first sheet and paste into the first available row in the secon
sheet. I then have to go baxk to the first sheet and delete the ro
from which I cut the data.
I would like to automate this so that by changing the one column to
closed status, it moves to the second and removes the empty row whe
finished.
In the one workbook where I have seen this function (can't figure ou
the code or copy it) the status column has something similar to
validation list.
Also, using one of the highlight codes from my other post produces a
error that requires debug to fix but once again I am a noob and don'
understand how to fix it. Thanks in advance for any help
 
Test this out against a copy of your workbook -- or don't save it if it doesn't
work the way you want!

Rightclick on the worksheet where you type "closed".
click on view code
paste this in:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ClosedWks As Worksheet
Dim DestCell As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

Set ClosedWks = Me.Parent.Worksheets("Closed")

On Error GoTo errHandler:

With Target
If StrComp(.Value, "closed", vbTextCompare) = 0 Then
'You just changed something to Closed!
With ClosedWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
With .EntireRow
Application.EnableEvents = False
.Copy _
Destination:=DestCell
.Delete
Beep
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

Now some housekeeping:

I called the worksheet getting the Closed info "closed". Change that if
necessary.

I also use column A as the column that gets the entry with this line:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

And I used column A to determine the next open row in the "closed" worksheet.
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)

Modify to match your data.
 
Back
Top