Forum Discussion
macro move line from one sheet to another
Hi all,
I have never made a macro befor and i need some help.
I need a macro Which is triggered by putting a name in the drop-down menu in column "L".
I have 3 names to be sorted into 3 different sheets. can someone help me with that, thanks in advance.
-Adrian
11 Replies
Sub three_names() Dim i As Long Dim k As Long Dim l As Long l = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To l Select Case Cells(i, 1).Value Case Is = "A" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 10)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) Case Is = "B" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 10)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) Case Is = "C" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 10)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) End Select Next i End SubMaybe with this code. In the attached file you can click the button in cell K2 to run the code. Assume that the 3 names are A, B and C. These names are in column A in the sheet "Tabelle1". In columns B to I are random numbers for this example. The macro copies each name and the data in the rows to the designated sheet "nom de site A", "nom de site B" or "nom de site C". This can be adpated to your requirements.
- Thanks for the reply, but my list should be a "to do" list. so when I name A it should go to sheet A and disappear from the priority list. also further for B and C.
can't open the macro either when I'm at work.Sub three_names() Dim i As Long Dim k As Long Dim l As Long l = Range("A" & Rows.Count).End(xlUp).Row For i = 2 To l If Cells(i, 10).Value = "remove" Then Select Case Cells(i, 1).Value Case Is = "A" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 9)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) Rows(i).Delete i = i - 1 Case Is = "B" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 9)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) Rows(i).Delete i = i - 1 Case Is = "C" k = Worksheets("nom de site " & Cells(i, 1).Value).Range("A" & Rows.Count).End(xlUp).Row Range(Cells(i, 1), Cells(i, 9)).Copy Destination:=Worksheets("nom de site " & Cells(i, 1).Value).Cells(k + 1, 1) Rows(i).Delete i = i - 1 End Select Else End If Next i End SubMaybe with this code. You can enter "remove" in column J ( in one or many cells) and click the button in cell K1. The row then is copied to the designated sheet and then it is deleted from the "to do" sheet.
