Code to move rows into another sheet based on certain values

Eagleeyes011
Posts
4
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
March 5, 2022
- Feb 9, 2022 at 05:59 AM
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
- Mar 5, 2022 at 06:36 PM
I found this lovely little script on this forum. It has worked flawlessly. I have a spreadsheet with multiple pages and I would like this code to work on all of them, but I need to change the destination sheet. I tried changing “Sheet2” to the page I wanted it to go to, but I get an error code. I thought I took a picture to share, but I can’t find it. For the original post, see link. I stole the name to the question also, just trying to keep continuity. I’m definitely no expert, so I humbly ask for someone’s help who is.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Count > 1 Then Exit Sub
If Target.Value = vbNullString Then Exit Sub
If Intersect(Target, Columns("E:E")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

If Target.Value = "Cancelled" Then
Target.EntireRow.Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete
End If

Sheet2.Columns.AutoFit

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub





https://ccm.net/forum/affich-970076-code-to-move-rows-into-another-sheet-based-on-certain-values



Thank you so much,
Sean.

3 replies

vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Updated on Feb 10, 2022 at 03:31 AM
Hello Sean,

I have a spreadsheet with multiple pages and ........


I'm assuming that you have a workbook with multiple worksheets from which you'd like the code to work.

To have it work from any worksheet (excluding the destination worksheet), the code would have to be altered a little as follows:-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

        Dim ws As Worksheet: Set ws = Sheets("Main") '---->Change destination sheet name to suit.
        
        If Intersect(Target, Sh.Columns("E:E")) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub
        If Sh.Name = "Main" Then Exit Sub

Application.ScreenUpdating = False
Application.EnableEvents = False

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

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub


You'll note that the above code is a Workbook_SheetChange event code and not a Worksheet_Change event code so the one code will work in all source worksheets.

In the code, I've declared a worksheet variable (ws) and assigned an assumed worksheet name to the variable, viz. "Main". You'll just need to change "Main" in the code to whatever your destination sheet name is.
The code will not work in the "Main" sheet.

To implement this code:-

- Right click on the "Main" sheet tab.
- Select "View Code" from the menu that appears. You should now be in the VB Editor.
- Over to the left in the project Explorer, double click on ThisWorkbook.
- In the big white code field to the right, paste the above code. You are now ready to go.

Test the code in a copy of your workbook first.

I hope that this helps.

Cheerio,
vcoolio.

P.S. I've removed your previous thread for you.
1
Eagleeyes011
Posts
4
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
March 5, 2022

Feb 10, 2022 at 07:40 AM
Ahh… thank you on multiple levels, including my first post, which was interestingly responding to your post that I linked to. Also your the person that had the original code that it tried! Sweet!

So the original code posted at the link I shared won’t work with the multiple worksheets?

Yes, workbook with multiple worksheets. I’d like to move all the canceled, or in my case “closed” items to another worksheet inside of that workbook (all excel, I hope my terminology is correct) for historical purposes.

I have made a macros to create a report from this workbook (totally separate from this workbook, but linked to it) and it’s starting to get cluttered with all the “closed” items, even with sorting, it’s becoming too large of a report, and paper is being wasted printing the historic items. I guess I could have worked the report macros workbook to not print those pages, but the user workbook is being used by multiple users to identify Deficiencies in individual shops, and is getting cluttered in general. I love that I’ve been able to share the workbook so multiple users can edit at the same time. So far no issues there. But that’s another story.

I’ll try this code as soon as I can, probably at the beginning of next month, then get back if I have any issues from there.

Thanks so much,
Sean
0
Eagleeyes011
Posts
4
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
March 5, 2022

Mar 5, 2022 at 12:24 PM
This is the code to rule them all!! If I could upvote this more, I would. I just tried the code today.

I made a couple of edits, such as column where the info is being sourced from, and changed cancelled to closed, and made the sheet "Historical" where I wanted the information to go to.

How can I get the code to recognize the information that is already there?

Column H is where the trigger is, and it is not formatted to select only Closed or Open as an input, so all the user inputs are a little different.
Is there any way to modify the "Closed" in this code to capture more variations of closed? Closed, closed, CLOSED, etc.. or would it be easier to make the column selectable. Closed or Open?

If that is the easier option, how can I change the column without loosing the data already there, and make it selectable to Closed/Open only?

This workbook is about 5 years old already and has a lot of information, if I have to hand jam the edits I will (search and replace). Any suggestions would be greatly appreciated. You've been such a great help already with the code. Thank you.
0
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Mar 5, 2022 at 06:36 PM
Hello Sean,

Is there any way to modify the "Closed" in this code to capture more variations of closed? Closed, closed, CLOSED, etc.. or would it be easier to make the column selectable. Closed or Open? 


To cover the variances in grammatical punctuation and as long as 'Closed' is always at least spelled correctly, just place this line:

Option Compare Text


directly above this line:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


Hence, no matter how 'Closed' is actually entered (Closed, CLOSED, cLOsed etc.....), the code will work for you.

You can, of course, use data validation in Column H and re-write the criteria exactly as you want them to be. This way, any User will only be able to select the options as you (the Boss?) have determined. To re-write an entire column with data validation would take you approximately two minutes.

I hope that this helps.

Cheerio,
vcoolio.
1
vcoolio
Posts
1345
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 20, 2022
249
Feb 11, 2022 at 12:56 AM
Hello Sean,

So the original code posted at the link I shared won’t work with the multiple worksheets? 


The only way it would work with multiple worksheets would be to place it in the worksheet module of each and every source sheet. Hence, you should be able to see the benefit of using the modified code in my post #1.

Please note that you'll need to totally remove the original code before implementing the new code other wise both codes will execute at the same time when entries are made in it's particular sheet. This could cause a little mayhem for you.

In the meantime, create a copy of your workbook simply to test the modified code before implementing it into your actual workbook next month.

Cheerio,
vcoolio.
0
Eagleeyes011
Posts
4
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
March 5, 2022

Feb 11, 2022 at 07:19 AM
Sounds great. Thank you very much. I’ll plug this into my test worksheet before I alter my live worksheet. I appreciate the help, and will get back with my results. Thank you again!!
0