Solved Copy Rows from Month Sheets to Main Sheet Based on Criteria

May 5, 2016 at 03:14:10
Specs: Windows 7
 Hi all,I have an excel document with 14 sheets. (Twelve individual months, unknown payments, duplicate payments). They all make use of the same headings (A4:L4). Column K is a drop down menu.I am looking to copy a row from the month sheets to either the unknown payments or duplicate payments sheets based on the selection from column K (either Unknown or Duplicate respectively).I don't have any experience using VBA. Is anyone able to help?

See More: Copy Rows from Month Sheets to Main Sheet Based on Criteria

#1
May 5, 2016 at 14:17:39
 When you say "Column K is a drop down menu" do you mean that each cell in Column K contains a drop down? (A "column" can't be a drop down menu but a individual cell can contain one.)

Report •

#2
May 5, 2016 at 15:43:00
 Hi, the value of each of the cells in column k can only be selected from a drop down menu. The range for the drop down menu is currently listed in column n

Report •

#3
May 5, 2016 at 16:22:40
 1 - Copy the following code to your clipboard2 - Right click the sheet tab for the sheet you want the copy to occur in and choose View Code3 - Paste the code into the pane that opens.4 - Repeat paste for each monthly sheet```Private Sub Worksheet_Change(ByVal Target As Range) 'Determine if change was made to Column K If Target.Column = 11 Then 'If Duplicate was chosen, copy Row to Duplicate Payments sheet If Target = "Duplicate" Then nxtRw = Sheets("Duplicate Payments").Range("K" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy Sheets("Duplicate Payments").Range("A" & nxtRw) End If 'If Unknown was chosen, copy Row to Unknown Payments sheet If Target = "Unknown" Then nxtRw = Sheets("Unknown Payments").Range("K" & Rows.Count).End(xlUp).Row + 1 Target.EntireRow.Copy Sheets("Unknown Payments").Range("A" & nxtRw) End If End If End Sub```

Report •

Related Solutions

#4
May 6, 2016 at 01:49:48
 Thanks for that!Sorry to seem stupid, but do I have to run this manually, or should it automatically copy the information to the relevant sheet once data has been entered into the row?I have just tried a test row and nothing seemed to happen.Thanks again!

Report •

#5
May 6, 2016 at 04:02:24
 It should run automatically. It does for me.The drop downs must be in Column KThe selections must be either Duplicate or Unknown (exactly)The sheet names you are copying to must be Duplicate Payments or Unknown Payments The code must be stored in the sheet module for every sheet that you are copying from.

Report •