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

Report •

#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.)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
1 - Copy the following code to your clipboard
2 - Right click the sheet tab for the sheet you want the copy to occur in and choose View Code
3 - 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

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 K
The 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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question