Apr 29 2019 02:22 PM - edited Apr 29 2019 02:23 PM
I have found so many different codes for this it's ridiculous. I don't think it should be very complicated. I want to move an entire row of data in sheet 1 based on the status of column "C" in that row, in sheet 1 , to sheet 2. I want the rows in sheet 2 to be a running list- IE not keep overwriting. Can someone please help me?
Apr 29 2019 11:05 PM
Apologies this code is a bit rough, I'm not a fan of hardcoding cell references and counters but hopefully this gives you an idea and you can tweak it to suit
Sub MoveToSheet2() Dim i As Integer Dim RowToRemove As Range For i = 1 To 1000 Sheet1.Range("C1").Offset(i, 0).Select If Selection.Value = "move" Then Set RowToRemove = Selection Sheet2.Range("A1").EntireRow.Insert Selection.EntireRow.Copy Sheet2.Range("A1") RowToRemove.EntireRow.Delete i = i - 1 End If Next i End Sub
Apr 30 2019 10:17 AM
@Wyn Hopkins thank you but that code didn't do anything.
I need rows 2, 4 and 5 to move to sheet 2 based on the Status of "new" being selected. This will be selected by a drop down box list of data. please help as I don't really know anything about coding
Date | Name | Status | ||
10-Jan | Bob | New | ||
10-Feb | Sue | Closed | ||
12-Feb | Jan | New | ||
13-Dec | Frank | New | ||
DATA | ||||
New | ||||
Closed | ||||
Dead | ||||
Apr 30 2019 04:40 PM
Apr 30 2019 04:47 PM
@Wyn Hopkins yes I understood that much. I enter this code on sheet 1 and it does not do anything, having changed move to "New"
Apr 30 2019 04:50 PM
@Wyn Hopkins yes i understood this. The code did not work:(
Apr 30 2019 04:56 PM
May 01 2019 09:09 AM
May 01 2019 09:18 AM
@Wyn Hopkins appreciate your help!
May 01 2019 03:32 PM
May 01 2019 04:05 PM
May 01 2019 05:07 PM
May 02 2019 08:42 AM
May 02 2019 03:22 PM
May 08 2019 02:32 PM
@Wyn Hopkins ugh I don't understand why the code doesn't work for me. Having the button works but I would rather not have to have the user press this. I would like the rows to automatically move based on their selection. Any other thoughts as to why it isn't working?
May 08 2019 04:23 PM
May 08 2019 06:25 PM
May 08 2019 08:59 PM
May 09 2019 06:09 AM
First step is to name column C as StatusColumn in the name manager box (this avoids you code breaking should someone insert a column to the left in the future)
Then put this code in your sheet1 VBA window
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim RowToRemove As Range 'avoid triggering code if New is typed in any other random cell expect for inside the StatusColumn If Intersect(Target, Range("StatusColumn")) Is Nothing Then Exit Sub End If 'avoid error if entire column is selected If Target.Rows.Count > 1 Then Exit Sub End If 'Relies on the idea that user will press Enter after typing "New" which shifts the cursor down 'and therefore the row above (offset -1) is the one to remove If Target.Offset(-1, 0).Value = "New" Then Set RowToRemove = Selection.Offset(-1, 0) Sheet2.Range("A1").EntireRow.Insert RowToRemove.EntireRow.Copy Sheet2.Range("A1") RowToRemove.EntireRow.Delete Set RowToRemove = Nothing End If End Sub
This code does assume that the user will type New and press Enter to move the cursor down to the next cell. Some users do change that Enter behaviour to jump right instead of down which will result in the wrong row being moved.
With time I'd investigate a more robust solution but this is the best I can do for now.
Hope it helps
Wyn