Copy rows to rows on another worksheet if " ✔ " [Solved]

CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 5, 2018 at 10:22 PM - Latest reply: vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Oct 12, 2018 at 01:23 AM
Hi everyone,
Ultimate greenhorn here.
I have just recently discovered the " Conditional Formatting " as well as VBA function on excel a week ago and am having troubles. I tried several codes that was provided on some forums but it doesn't seem to work.

Situation :

I have 2 sheets " K1 " and " Completed "
I would like to duplicate the whole row on " K1 " from cells C5 to Z5 to the sheet named " Completed " if cell Z5 on sheet " K1 " is marked as ✔ or X meaning that it has been completed. It should work not only for this range but C6 to Z6, etc as it goes down the list, as long as it is marked as ✔ or X on column Z.

Is it also possible to make it chronological too based on the dates ?

Not sure if I am asking too much, but your help is greatly appreciated sensei's !
See more 

Your reply

19 replies

vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 6, 2018 at 02:57 AM
0
Thank you
Hello CassYCS,

Try the following code placed in a standard module and assigned to a button:-
Sub Test()

     Dim ws As Worksheet: Set ws = Sheets("K1")
     Dim ws1 As Worksheet: Set ws1 = Sheets("Completed")

Application.ScreenUpdating = False

With ws.Range("Z4", ws.Range("Z" & ws.Rows.Count).End(xlUp))
     .AutoFilter 1, "X", xlOr, ChrW(&H2713)
     ws.Range("C5", ws.Range("Z" & ws.Rows.Count).End(xlUp)).Copy
     ws1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
     'ws.Range("C5", ws.Range("Z" & ws.Rows.Count).End(xlUp)).EntireRow.Delete
     .AutoFilter
End With

ws1.Range("A2", ws1.Range("X" & ws1.Rows.Count).End(xlUp)).Sort ws1.[A2], 1

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


The code assumes that your data starts in Column C, row5 with headings in row4.
The transferred data is sorted by date ascending in the "Completed" sheet.
If you want the "used" data from the "K1" sheet deleted after each transfer then remove the apostrophe(') from in front of the green coloured line of code (line 12).

Please test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 6, 2018 at 03:21 AM
Hi Vcoolio,


Where do I put the button ? I tried it on both "K1" and "Completed " but this keeps popping up.

Am I doing something wrong ?

A million thanks for your reply !

Sincerely,
CassYCS
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 6, 2018 at 03:27 AM
0
Thank you
Hello CassYCS,

You had best upload a sample of your workbook for us to work with.

Upload your file to a free file sharing site such as ge.tt or Drop Box then post the link to your file back here. Make sure that your sample is an exact replica of your actual workbook. If your data is sensitive then please use dummy data.

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 6, 2018 at 04:01 AM
Dear VCoolio,

Link below on dropbox as requested.
What I want to to is to basically transfer those which will have a symbol on Z5 depicting that it is complete into the " Completed " worksheet for easier reference in the future.

https://www.dropbox.com/s/kgjum08ntg5wbee/Shipment%20Tracker%202018%20%28%20Dummy%20names%20%29%20.xlsx?dl=0

Sincerely,
CassYCS
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 6, 2018 at 04:15 AM
Dear VCoolio,

If you have any suggestions to help simplify the process, do let me know.
I'm open to ideas as it looks quite messy at the moment.

Sincerely,
CassYCS
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 6, 2018 at 05:06 AM
0
Thank you
Hello CassYCS,

You don't have a "Completed" sheet which is part of the reason you received the error. The other reason is that you don't have a "K1" sheet but you have a sheet named "K1 Bintulu Port". In Excel, if you wish to use VBA codes, everthing needs to be precise.

I'll get back to you once I've had a chance to properly look through your workbook.

Cheerio,
vcoolio.
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 6, 2018 at 07:46 AM
0
Thank you
Good evening CassYCS,

I've had a good look through your workbook but I have been left confused.

Which is your main sheet, K1 "Bintulu Port" or "K1 KK Port"?
As I stated in my last post, there isn't a "Completed" sheet. Do you intend to create one or do you intend to use one of the "Closed" sheets.

As for the criteria, rather than a tick mark or an "X", I believe it would make things simpler if you had one criteria in Column Z. For example, a simple "Yes" or "No". You could create drop down boxes in each cell with "Yes" or "No" as the selection criteria.You would only have to change the heading to "Closed (Y/N)". Not over-complicating worksheets makes programming smoother and seamless.

Please advise.

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 6, 2018 at 09:18 PM
Dear VCoolio,

There used to be a K1 and Completed but I modified it before sending it to you , but when I used it in the past, I amended the code as necessary. You can program it however you deem fit. There are CLOSED sheets for each, or you may make a separate sheet.

What I want is basically just a record for all the closed shipments so that I can refer to them in the future when the need arises. Sorting them out to location such as Bintulu or KK would be more precise but if you think just a general one is good, is alright with me too.

Whichever you think is better, I leave it in your hands kind sir, hahah.

Sincerely,
CassYCS
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 6, 2018 at 09:21 PM
Dear VCoolio,

Bintulu , KK and K3 are locations of our shipments.
For the cells which are marked as closed or completed at Z5, I want them to automatically duplicate or cut them into the separate sheet for shipments which have been completed. You can create a new one or use the "Closed" sheets I created for each location.
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 7, 2018 at 01:47 AM
0
Thank you
Hello CassYCS,

I've tried to rationalise the sample workbook a little as you can see from the example at the link below.

http://ge.tt/1g46aSr2

I've created a Completed sheet for the K1 Bintulu Port and a Completed sheet for the K1 KK Port.

Now, each time a "Y" is placed in Column Z and you click away (or press enter or down arrow), the entire row of data is transferred to its relevant Completed sheet. The relevant row of data is then deleted from its source sheet.

I've changed the coding as follows:-

To be placed in the K1 Bintulu Port sheet module:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

        Dim ws As Worksheet: Set ws = Sheets("Completed Bintulu Port")

If Intersect(Target, Columns(26)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Y" Then
        Target.EntireRow.Copy
        ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        Target.EntireRow.Delete
        ws.Columns.AutoFit
        ws.Rows.AutoFit
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


To implement this code:-

- Right click on the K1 Bintulu Port sheet and select "View Code" from the menu that appears.
- In the big white field that then appears, paste the above code.

To be placed in the K1 KK Port sheet module:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

        Dim sh As Worksheet: Set sh = Sheets("Completed KK Port")

If Intersect(Target, Columns(26)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Y" Then
        Target.EntireRow.Copy
        sh.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        Target.EntireRow.Delete
        sh.Columns.AutoFit
        sh.Rows.AutoFit
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Implement this into the K1 KK Port sheet module using the same steps as for the K1 Bintulu Port sheet.

Test all this in a copy of your actual workbook first but you'll have to rationalise it as per the sample.

Let us know if this works for you.

Cheerio,
vcoolio.
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 7, 2018 at 02:14 AM
Apologies CassYCS,

I've had to amend the codes a little as I've just realised that you are not using Column A.

For the K1 Bintulu Port sheet module:-


Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

        Dim ws As Worksheet: Set ws = Sheets("Completed Bintulu Port")

If Intersect(Target, Columns(26)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Y" Then
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "Z")).Copy
        ws.Range("B" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        Target.EntireRow.Delete
        ws.Columns.AutoFit
        ws.Rows.AutoFit
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


For the K1 KK Port sheet module:-

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

        Dim sh As Worksheet: Set sh = Sheets("Completed KK Port")

If Intersect(Target, Columns(26)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Y" Then
        Range(Cells(Target.Row, "B"), Cells(Target.Row, "Z")).Copy
        sh.Range("B" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
        Target.EntireRow.Delete
        sh.Columns.AutoFit
        sh.Rows.AutoFit
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub


Here is the link to the updated sample file:-

http://ge.tt/90cgbSr2

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 7, 2018 at 02:41 AM
Dear VCoolio,

It works wonderfully.
However it pastes itself onto Cell A1 instead of cells B5 ( Numbering ) to Z5 ( Where the Y will be ).

What do I have to change in order to achieve this ?

I tried changing this based on what I knew but it couldn't work.

sh.Range("B" & Rows.Count).End(5)(4).PasteSpecial xlPasteAll

Thank you so much for your help though VCoolio !

Sincerely,
CassYCS
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 7, 2018 at 02:47 AM
Did you look at my post #12?
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Sep 7, 2018 at 02:52 AM
Dear VCoolio,

I just received it.
Works like a charm ! Thank you so much !
I need to start learning more about vba too, comes in handy.

Appreciate your help.

Sincerely,
CassYCS
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Sep 7, 2018 at 03:19 AM
0
Thank you
Hello CassYCS,

You're welcome. I'm glad that I was able to help.

If you study the codes carefully, you should be able to adapt them in future to any more "Port" sheets that you may need to create.

Good luck with it all.

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Oct 11, 2018 at 12:00 AM
Hi V,

There seems to be a problem now after I finally have more than 1 complete shipments. It all pastes to the same row and overlaps each other. Ex : Row 1A, I have 5 shipments but it only shows on that one line. If Num 1 is done, it goes to Row 1A, then when number 2 is done, it overlaps the previous shipment at Num 1 and so on.


Code is as below :

Option Compare Text
Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet: Set ws = Sheets("Completed KK Port")

If Intersect(Target, Columns(27)) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Y" Then
Target.EntireRow.Copy
ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll
Target.EntireRow.Delete
ws.Columns.AutoFit
ws.Rows.AutoFit
End If

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
Respond to vcoolio
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Oct 11, 2018 at 04:40 AM
0
Thank you
Hello Cass,

If Intersect(Target, Columns(27)) Is Nothing Then Exit Sub 


Your previous posts showed the criteria column as being Column 26 (Z).


ws.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlPasteAll 


Why are you pasting to Column A when all your data starts in Column B?

Perhaps you didn't read my post #12 after all.

Cheerio,
vcoolio.
CassYCS
Posts
11
Registration date
Wednesday September 5, 2018
Last seen
October 11, 2018
- Oct 11, 2018 at 09:55 PM
Hi V,

My mistake, I did for the other sheet but forgot to amend for this one.
  • Facepalm * thank you very much for your reply though.


Appreciate it.
vcoolio
Posts
1204
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
November 11, 2018
- Oct 12, 2018 at 01:23 AM
Hello Cass,

Good to know that you've worked it out.

Take care.

Cheerio,
vcoolio.
Respond to vcoolio