Code to move rows into another sheet based on certain values Closed

Question

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

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.

Code to move rows into another sheet based on certain values

3 responses

Similar discussions

Subscribe To Our Newsletter!