Copying specific condition meeting data from one worksheet to another

Question

I have three worksheets, one with a list of all the cheques i write, one with a list of all cheques from my personal account and one with a list of all cheques from my business account.

I dont know how to make all the cheques with name "Business" go to worksheet 3 and the ones with name "Personal" go to worksheet 2.

Example :

Worksheet 1

Name      Cheque No. Date     Amount Currency
Personal  1234567    10/8/13  10000  HKD
Business  1234567    9/8/13   10000  USD

Worksheet 2

Name      Cheque No. Date     Amount Currency
Personal  1234567    10/8/13  10000  HKD

Worksheet 3

Name      Cheque No. Date     Amount Currency
Business  1234567    9/8/13   10000  USD

did you try anything so far (which is recommended when asking a question here in SO)? — Kazimierz Jawor, Aug 07 '13 at 11:34
Checkout the use of Autofilter. [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) should get you started. If you are still stuck then post the code that you tried and the error that you are getting and then we will take it from there... — Siddharth Rout, Aug 07 '13 at 11:43

richie · Accepted Answer · 2013-08-07T12:08:24.767

Step 1: In your 'Worksheet 1' you could include one more column as 'Count' and do the following; enter image description here

Drag the formula down

Step 2:

In 'Worksheet 2' you could do this; enter image description here

ie For each column;

'Name' ="Personal"&ROW(P1)

'Cheque No' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,2,FALSE),"")

'Date' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,3,FALSE),"")

'Amount' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,4,FALSE),"")

'Currency' =IFERROR(VLOOKUP(A2,Sheet1!$B$2:$F$9,5,FALSE),"")

Drag the formulae down.

Step 3:

In 'Worksheet 3' do this;

enter image description here

Under the 'name' column replace 'personal' in the formula to 'business'. The rest would be the same.

Make sure to drag the formulae down to get your results

This is how my 'personal' (Worksheet 2) and 'business' (Worksheet 3) worksheets look like;

enter image description here

Sorry about the quality of images.

score 1 · Answer 2 · answered Aug 07 '13 at 12:16

Explore Siddharth and KazJaw's suggestions - they are seldom wrong.

A couple of common ways of tackling your problem:

1.

Create a pivot table in the sheet 2.
Create a pivot in sheet 3.
Each pivot should have the Name field in the pivots Report Filter - set one to Personal and the other to Business.
Use a named range in sheet 1 and set the data for each pivot to that named range.
When new data is added you just need to adjust the named range area and press refresh.

(the last bullet point above could be a very small macro)

2.

Alternatively use a macro something like the following:

Option Explicit

Sub MoveToOtherSheets()

With Excel.ThisWorkbook.Sheets("Sheet1")
    Dim cell
    For Each cell In .Range(.Cells(2, 1), Cells(.Rows.Count, 1).End(Excel.xlUp))

        If UCase(cell(1, 1)) = "PERSONAL" Then
            With Excel.ThisWorkbook.Sheets("Sheet2")
                cell.EntireRow.Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
            End With
        End If

        If UCase(cell(1, 1)) = "COMPANY" Then
            With Excel.ThisWorkbook.Sheets("Sheet3")
                cell.EntireRow.Copy .Cells(.Rows.Count, 1).End(Excel.xlUp)(2, 1)
            End With
        End If
    Next

End With

End Sub

Copying specific condition meeting data from one worksheet to another

2 Answers2