How to move data from one sheet to another if a condition is met

[loulou1991]

Hello,

I need some help please with creating some sort of formula (or code? I don't know) to move data on my spread sheet from one sheet to another, depending on certain conditions.

I have a workbook with a sheet called 'New Starters' where all my current data is. This table spans from column A to N and has varying rows depending on how many new starters there are. I then have 12 other sheets which are named by each month.

In the New Starters sheet, column N is left blank until the person has started, I then write 'Yes'. Basically, once 'Yes' is written in this column, I want the entire row of data transferred to the relevant months sheet. The month on the New Starter sheet is located in column K. Ideally the data would be removed from the New Starters sheet once transferred to the month.

All the column headers are the same on each sheet.

Is this possible? I only learnt the word 'macro' when it comes to excel yesterday so any very simplified responses would be greatly appreciated!

Thanks!

 

[WarPigl3t]

Why can't everyone who has a question on this forum be as consise as you? That was a perfect explication of what you have and what you want to accomplish and you didn't even have to post a table to this thread for me to get it. Thank you. I trust you know what to do with this code:

Worksheet "New Starters" Code:

Private Sub Worksheet_Change(ByVal Target as Range) 
     Call myMacro
End Sub

Module Code:

Sub myMacro()
     masterSheet = "New Starters"
     monthColumn = "K"
     yesColumn = "N"
     r = 2
     lastRow = Sheets(masterSheet).Range(monthColumn & Rows.Count).End(xlUp).Row
     Do Until r > lastRow
          If Sheets(masterSheet).Range(yesColumn & r).Value = "Yes" Then
               myMonth = Sheets(masterSheet).Range(monthColumn & r).Value
               For Each wksht in WorkSheets
                    If wksht.Name = myMonth Then
                         printRow = Sheets(wksht.Name).Range(monthColumn & Rows.Count).End(xlUp).Row + 1
                         Rows(r).Copy
                         Application.Paste Destination:= Sheets(wksht.Name).Range("A" & printRow)
                         Application.CutCopyMode = False
                         Rows(r).Delete
                    End If
               Next wksht
          End If
          r = r + 1
     Loop
End Sub

 

[WarPigl3t]

Where to put the "Worksheet New Starters Code":
The sheets are at the bottom of your opened excel workbook. You have a sheet called "New Starters". Right Click the sheet called "New Starters". A menu appears. Select where it says "View Code". A VBA editor appears. This is the worksheet code area where you will copy paste my code into. Do not paste the "Module" code that I gave you here. Only paste the Worksheet code I gave you.

On to the next step. You'll need to create a module to paste the "Module" code I gave you. On the left of the VBA editor, you'll see what looks like a navigation menu. You'll see your sheet names there, but more importantly, those sheets are inside of a folder called VBA project or something like that. Right click the "VBA project". A menu appears. The "Add Module" or "Insert Module" is what you are looking for. Find it within one of the sub menus. A new code area appears. This is called Module1. Insert my "Module" code into this VBA editor. You are now done.

 

[My Aswer Is This]

I believe it is safer to use a double click to activate your Macro. A simple cell change event is dangerous in my opinion in that if you make a simple mistake in column "N" then your script will automatically run.
My script here requires you to double click on column "N" and then that rows data will be copied to the sheet identified in column "K" and then delete the row you double clicked on. It's difficult to make a mistake and double click on a particular cell.

To install this code:

Right-click on the sheet tab "NewStarters"
Select View Code from the pop-up context menu
Paste the below code in the VBA edit window.
Your workbook will need to be Macro Enabled.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("N:N")) Is Nothing Then
Cancel = True
On Error GoTo J
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Sheets(Cells(Target.Row, 11).Value).Cells(Rows.Count, "K").End(xlUp).Row + 1
Rows(Target.Row).Copy Destination:=Sheets(Cells(Target.Row, 11).Value).Rows(Lastrow)
Rows(Target.Row).EntireRow.Delete
Application.ScreenUpdating = True
End If
Exit Sub
J: MsgBox "We had an Error. You may not have a sheet named properly"
End Sub

 

[WarPigl3t]

You could do what "My Answer Is This" says. I'm sure it'll be fine. The on change event code will run every time something changes in your worksheet; however, nothing will be moved to your other sheets unless the word "Yes" is in a row in column "N". So there is nothing to worry about there. But here is the thing. When a macro runs, the "Undo" button becomes disabled. Which means if you make a mistake such as accidentally deleting a bunch of stuff because your dog pushed the delete key on one of the cells, the cell has now changed which means the code will run and you won't be able to undo it. The safest thing would be to not use the on change event code. You don't even have to use the double click code that "My Answer Is This" provided. You can just insert a button and assign the "Module" code that I provided to it.

 

[vcoolio]

Hello Loulou,

The safest thing would be to not use the on change event code. You don't even have to use the double click code that "My Answer Is This" provided. You can just insert a button and assign the "Module" code that I provided to it.

I'm with WarPigl3t on this one. For what it's worth, here's my contribution:-

Sub TransferData()


Application.ScreenUpdating = False


Dim lRow As Long
Dim i As Integer
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row


Sheet1.Select


For i = lRow To 2 Step -1
          MySheet = Cells(i, 11).Value
          If Cells(i, 11) <> "" And Cells(i, 14) = "Yes" Then
          Range(Cells(i, 1), Cells(i, 13)).Copy Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
          Range(Cells(i, 1), Cells(i, 14)).Delete
    End If
Next


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

The above code can just be assigned to a button as per my test work book in the simplified sample at the following link:-

https://www.dropbox.com/s/plg2a7fwel4seb7/Loulou1991.xlsm?dl=0

I hope that this helps.

Cheerio,
vcoolio.