CODEWITHSUNDEEP
excelvba
Abishek Puri
Abishek Puri

Reputation: 27

Copying specific condition meeting data from one worksheet to another

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

Upvotes: 2

Views: 14763

Answers (2)

whytheq
whytheq

Reputation: 35605

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

Upvotes: 1

richie
richie

Reputation: 18648

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

enter image description here

Sorry about the quality of images.

Upvotes: 1

Related Questions