Automatically move rows to another worksheet based on cell value

Hi,

I need a macro to automatically move entire rows based on cell value within that row, and automatically delete the empty row that was left behind. I have been looking at other posts, and I haven't been able to successfully figure it out. Please help me! The worksheet from which the data will come is named "Pickup Screen" and the sheet I need it go move to is called "Cost Management." The cell value is called "Delivered" in Column P. Thanks!!

Thank you for replying so quickly! Unfortunately, I got an error message that said "Can't Execute Code in Break Mode." Any idea how to fix it?... Thanks again!

you would have to edit the macro and press the stop button after taking out any breaks in the code.
if that fails, post a sample with the macro included..

Unfortunately, I tried to upload the file and I got an error message that the file is too large to for the Forum. Is there some other way for me to get it to you?...

Thanks!

just send me a portion of it...

Okay, a worksheet with just the bare bones is attached... Thanks again for your great help on this!

Hi RCM,

Have you had a chance to look at this? I'm DYING here!!! (LOL). If you can, please give me some guidance....

Thanks!

Jon

OK I had to clean up a statement in the code provided
the macro is included and ran in the attached file yet there is no data to be tested on

Out of State Pickup Management Tool with Data.xlsm

Hi RCM,

I tried the VB code, and unfortunately, it didn't work. I have attached another version with data so we can test it. Again, I can't tell you how much I appreciate the help!...

Thanks!

Jon

I tweaked the macro and played with it

Hi RCM,

This works great! Thank you!! The only thing I'm still struggling with is how to create the button in the Pickup Screen called "Button 2." I'm trying to copy/paste the macro into my actual file (remember, the actual file was too large to upload). The only thing I don't understand is the button. Can you help my understand how to put the button in my file?...

Thanks a million, RCM!!

Jon

OK do you have a DEVELOPER Option? (it should be located to the right of the VIEW option)

Let's say you do if not (FILE->OPTION->CUSTOMIZE RIBBON-> Check Developer on the Main Tabs)

Copy the macro into the file

Select the DEVELOPER option

Select the insert Option

Select the little rectable located in the upper right hand corner

Place it accordingly

It will ask to assign a macro.

Select the macro

Press OK

OR .... copy your data into the file


voila!

RCM, you are a blessing from Heaven!! Everything works!! Now, because of my own stupidity, I realized I created another problem. On the file I sent you, for some reason Columns A ("Called In") and B ("Pieces") are missing from the Cost Management tab. Therefore, when the data is moved over, obviously, it populates the wrong cells. I know I've been a pain in the ***, but can you help me fix this one last thing?...

Thanks!!

I adjusted the code to copy the record correctly. I highlighted the changed code

Sub movearound()

Dim r, r2, rmax, rw

datasheet = "Pickup Screen"
outsheet = "Cost Management"

Application.ScreenUpdating = False


r2 = Sheets(outsheet).Range("A" & Rows.Count).End(xlUp).Row + 1
r = 9

While r <= Sheets(datasheet).Range("A" & Rows.Count).End(xlUp).Row
If Sheets(datasheet).Range("P" & r) = "Delivered" Then

For c = 1 To 16
Sheets(outsheet).Cells(r2, c) = Sheets(datasheet).Cells(r, c)
Next c

Sheets(datasheet).Select
Rows(r & ":" & r).Select
Selection.Delete Shift:=xlUp
r2 = r2 + 1
r = r - 1
End If
r = r + 1
Wend
Sheets(datasheet).Select
Columns("A:Z").AutoFit

Application.ScreenUpdating = True

End Sub

RCM, you are FANTASTIC!! Thanks so much for your help. Everything works!!