Code to move rows into another sheet based on certain values
Closed
Eagleeyes011
Posts
29
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
September 6, 2024
-
Feb 9, 2022 at 05:59 AM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Mar 5, 2022 at 06:36 PM
vcoolio Posts 1411 Registration date Thursday July 24, 2014 Status Moderator Last seen September 6, 2024 - Mar 5, 2022 at 06:36 PM
Related:
- Forum.it have
- Forum.it sign - Best answers
- Forum.it law - Best answers
- Battery reset code - Guide
- How to get whatsapp verification code online - Guide
- Samsung volume increase code - Guide
- Google sheet right to left - Guide
- Samsung keypad reset code - Guide
3 responses
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Updated on Feb 10, 2022 at 03:31 AM
Updated on Feb 10, 2022 at 03:31 AM
Hello Sean,
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:-
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Mar 5, 2022 at 06:36 PM
Mar 5, 2022 at 06:36 PM
Hello Sean,
To cover the variances in grammatical punctuation and as long as 'Closed' is always at least spelled correctly, just place this line:
directly above this line:
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.
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.
vcoolio
Posts
1411
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 6, 2024
262
Feb 11, 2022 at 12:56 AM
Feb 11, 2022 at 12:56 AM
Hello Sean,
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.
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.
Eagleeyes011
Posts
29
Registration date
Tuesday February 8, 2022
Status
Member
Last seen
September 6, 2024
1
Feb 11, 2022 at 07:19 AM
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!!
Feb 10, 2022 at 07:40 AM
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
Mar 5, 2022 at 12:24 PM
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.