copy data to new workbook & sort data into different categories if criteria match

[natnauk16]

Hello everyone,

I am new to Excel VBA and I would really appreciate some help with a project that I am working on.

I am trying to copy data from one workbook to another if it matches the criteria using VBA (instead of manually matching criteria in 1000+rows), to sort the data into ten categories.

I did spend some time reading up a few books and browsing through this forum but this project appears a lot harder than I have imagined. Thus far, I have only managed to find the some of the VBA codes but having a hard time to complete it and so, I would really appreciate your help. I have attached the sample data files to illustrate what I am trying to do and I hope that someone can offer me some help. Any help is greatly appreciated.

Many thanks in advance for your help,
Nat


Part 1 (Workbook "raw.xls")
====================

1. In worksheet "New", my data consist of 4 columns indicating the date and a 2-digit number in each column which ranges from 1 to 72.

Date 1st incident 2nd incident 3rd incident
----------------------------------------------------------------------------------
10/8/2011 1 1 2

2. Copy data from worksheet "New" to other worksheets according to the value in "1st incident" column ( a total of 72 worksheets)


Part 2 (Workbook "master.xls")
=======================

1. Copy data from "raw.xls" to "master.xls" according to the value in "1st incident" column ( a total of 72 worksheets)

2. Add to existing data already in "master.xls"
a. To do this, I know that I will need to use VBA code (FinalRow = Cells(Rows.Count, 1).End(xlUp).Row)

3. Add data to 2 locations in each worksheet (Range A-D and Range G-J)


Date 1st incident 2nd incident 3rd incident Date 1st incident 2nd incident 3rd incident
---------------------------------------------------------------------------------- ----------------------------------------------------------------------------------
1/1/2008 1 1 2 4/1/2008 1 2 11
10/8/2011 1 1 2 10/8/2011 1 1 2

4. In Range G-J, move data to Range A-D if the value in all 3 columns matches with another row
a. this is essentially to find duplicate values in "2nd incident" and "3rd incidents since value in "1st incident" is always the same in each worksheet
b. date column may have the same value (or it can have a different value)


Part 3 (Workbook "detail.xls")
=======================

1. Copy data from "master.xls" to "detail.xls" in each worksheet according to 4 categories.

1st Category : Prime numbers (1 2 3 5 7 11 13 17 19 23 29 31 37 41 43 47 53 59 61 65 67 71)
2nd Category : Odd numbers (9 15 21 25 27 33 35 39 45 49 51 55 57 63 69)
3rd Category : Even numbers (4 8 16 32 64)
4th Category : OddEven numbers (6 10 12 14 18 20 22 24 26 28 30 34 36 38 40 42 44 46 48 50 52 54 56 58 60 62 66 68 70 72)

Hence, there are 4 primary worksheets: Detail_Prime, Detail_Odd, Detail_Even and Detail_OddEven


2. Add to existing data already in "detail.xls"
a. To do this, I know that I will need to use VBA code (FinalRow = Cells(Rows.Count, 1).End(xlUp).Row)

3. Sort the list.

4. If the value in all 3 columns matches with another row, highlight date column with color and then copy data to secondary worksheets for each category.

a. Highlight the date column with color (note: there are 6 possible rows with matching values)

Date column color
----------------------------------------------------------------------------------------------------------------------
Same value in 2 rows example: Bold Font
Same value in 3 rows example: Yellow
Same value in 4 rows example: Red
Same value in 5 rows example: Green
Same value in 6 rows example: Brown


b. To do this, I know that I need to use VBA code (Cells(I, 1).Resize(1, 8).Interior.ColorIndex = 4)

c. Hence, there are 4 secondary worksheets: Prime2, Odd2, Even2 and OddEven2


5. In each secondary worksheet, add data from primary worksheet to existing list in Range A-D.

a. Highlight cell in each column with color according to 4 categories

Cell Color
----------------------------------------------------------------------------------------------------------------------
Prime number Pink
Odd number Yellow
Even number Red
OddEven number Green


b. Categorize the data according to 10 columns according to each category

1st category: All 3 incidents are prime numbers
2nd category: 2 Prime + 1 Odd
3rd category: 1 Prime + 2 Odd
4th category: 2 Prime + 1 Even
5th category: 1 Prime + 2 Even
6th category: 2 Prime + 1 OddEven
7th category: 1 Prime + 2 OddEven
8th category: Prime + Odd + Even
9th category: Prime + Even + OddEven
10th category: Prime + Odd + OddEven

c. Indicate value of "1" in each category

d. Calculate the total in each category