Need help with creating VB code for moving rows to different sheet based on cell valu

[mj1003]

Hi everyone,

I got a VB code from this site to move rows to different sheet based on cell value. It worked amazing for once, but if I run it twice, rows in target sheet are getting replaced with new ones or getting deleted.
Can you help me on making the code to look for next empty row?

Thank you so much in advance for helping me. I'm new to this forum and the code that I got was an amazing except that small issue that I couldn't fix.
Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Application.ScreenUpdating = False
' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("Facilities Requests")
Set Target = ActiveWorkbook.Worksheets("Completed Projects")

Application.ScreenUpdating = False
Target.Select

With Target
.Rows("2:1000").Select
Selection.Delete Shift:=xlUp
End With

Source.Select
j = 2 ' Start copying to row 1 in target sheet

For Each c In Source.Range("L2:L1000") ' Do 1000 rows
If c.Value = "Completed" Then
Source.Rows(c.Row).Copy Target.Rows(j)
Source.Rows(c.Row).Delete
j = j + 1
End If
Next c

Target.Select
Target.Range("A1").Select

Worksheets(1).Calculate

'Application.CutCopyMode = False
Source.Select
Source.Range("C5").Select
Application.ScreenUpdating = True
End Sub

[jolivanes]

It does not look like you got the code from this site. Too many "select" statements.
Could you just explain what you want to achieve?
Explain it short and sweet, don't leave anything out.
BTW, pack your code in code tags please.
How To Add Code Tags.jpg

[mj1003]

Thank you Jolivanes for replying.

I wanna move rows which has cell value of "completed" to different sheet.

The trouble that I have with this code is that it's overriding the already moved row instead of moving to next cell.

Please Login or Register  to view this content.

P.S., Hope I followed what you said. Thank you once again.

[jolivanes]

Try this on a copy of your workbook.

Please Login or Register  to view this content.

[mj1003]

It's giving out error
error.PNG

[jolivanes]

Can you attach a sanitized version of your workbook. By sanitized I mean no personal data. It should represent your workbook though.

[mj1003]

I've attached the sample file.

[jolivanes]

In the explanation in Post #1 you have "completed" with a small c and in your example code it is with a capital C which I missed.
In the code from Post #4, change the word completed to Completed or put "Option Compare Text" as your first line.
It would look like this.

Please Login or Register  to view this content.

Put the code in a copy of your workbook, save the workbook as a macro enabled (.xlsm) workbook and try the code again.
Let us know.

[mj1003]

Thank you Jolivanes!

The code is running but if I run the same code for second time it's overriding the previous copied version

-
MJ

[jolivanes]

That's because Column A is blank.
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[mj1003]

Thank you so much!

[mj1003]

Hi Jolivanes,
Thank you for helping me with the coding. My boss was really happy with the output.
I wanna update this code for one more attribute.
I wanna move rows into different sheets based on project start date.
So should I add a whole new "with" block for it?

-
MJ

[jolivanes]

Please explain what you want to achieve.
Same range to a different sheet, different range to same sheet, different range to different sheet or something totally different?

[mj1003]

Same range to different sheets based on the year





Sample.PNG

[jolivanes]

Do you want all the rows of data from each year in column "G" into it's own sheet?
What about the headers?
Maybe do it manually and attach the workbook again so we can see the before and after.

[jolivanes]

Is this what you have in mind? See attached

[mj1003]

I've shown on how excel sheet should look after I update project start date.

[jolivanes]

Is that not what the attachment in Post #16 does?

[mj1003]

I'm sorry for replying late.

Here is how it should look like.

-
Thank you!

[jolivanes]

Try the attachment from Post #16 and let us know what needs to be changed.

[mj1003]

Wow! This looks really amazing! Thank you so much Jolivanes!!!
I want the copied rows to be deleted from main page.

Also will it work if I add this program to the first one "Completed projects"?

[jolivanes]

Check if the attached is what you have in mind.
Remember, if you run code to see if it works, make sure to use a copy of your original. There is no "Undo" button for code. Gone is gone forever.

[mj1003]

It works in the sample sheet perfectly.
However I recently made changes on the top row to include all my macro buttons and total values. Because of this, the code is removing the header.

I've attached a sample sheet

[jolivanes]

Everything related to Facilities Request Sheet Row1 needs to be changed to Row2 and Row2 to Row3.

Change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

I have not ried the changes so after changing, try it on a copy of your original first.
You will need to go through the code so you understand what's happening with it.
There is no doubt that you will want to make changes in the future so it'll be so much easier and faster if you understand the code.

[mj1003]

It worked perfect!
Yeah. I am completely new to coding. I'm learning it with your codes and googling to understand.
This is definitely a great start point for me. I learned lot about VB and macros in last 2 weeks.
Thank you so much for helping me with this.

[jolivanes]

It was my pleasure.
Good luck and enjoy