How can i use a Macro Button to help transfer and sort data from one open workbook to another?

[mDgo0d4mE]

Hello,
Im new to the Macro scene and i enjoy learning with VBA lately

I've been having some trouble with moving raw data from one work book to another, i was able to move a few items but im stuck with using IF/than statements. ive gone through some threads to try and recreate some but i keep getting errors or i cant think logically. i created a test to run to see if someone might have some better experience/advice. (sorry for caps, coming from home computer)

I WOULD LIKE TO BE ABLE TO CREATE A MACRO THAT COPYS ALL THE DATA FROM AN OPEN SOURCE WORKBOOK TO A NEW TARGET WORKBOOK THAT IS A PRE-MADE TEMPLATE THAT HAS THE MACRO BUTTON ALREADY SAVED AND ALL I WOULD NEED TO DO IS HAVE OPEN THE TEMPLATE FILE AND THE SOURCE WORKBOOK RUN IT AND THEN SAVE AS A NEW FILE NAME.

<tbody>
</tbody>

HOLDER

ID

NAME

DATA1

DATA2

DATA3

DATA4

OLD

NEW

Y OR N

TYPE

INFO1

INFO2

INFO3

INFO4

INFO5

INFO6

INFO7

COUNT

0

1

Beaver

AA

AAIRPLANE

10

123

BEAN

BEATS

Y

UMBER

A

A

A

A

A

A

A

5

0

1

Beaver

AA

AAIRPLANE

10

123

CORN

BEATS

Y

APRICOT

A

A

A

A

A

A

A

10

0

1

Beaver

AA

AAIRPLANE

10

123

NONE

CORN

N

ORANGE

A

A

A

A

A

A

A

15

0

2

Bear

AB

ABOY

11

456

BEAN

BEATS

Y

CHOCOLATE

B

B

B

B

B

B

B

40

0

2

Bear

AB

ABOY

11

456

CARROT

BEATS

Y

ORANGE

B

B

B

B

B

B

B

20

0

2

Bear

AB

ABOY

11

456

CORN

BEATS

Y

APRICOT

B

B

B

B

B

B

B

25

0

2

Bear

AB

ABOY

11

456

NONE

BEATS

Y

UMBER

B

B

B

B

B

B

B

35

0

2

Bear

AB

ABOY

11

456

POTATO

BEATS

Y

B

B

B

B

B

B

B

45

0

2

Bear

AB

ABOY

11

456

TOMATO

BEATS

Y

APRICOT

B

B

B

B

B

B

B

30

0

3

Bunny

AC

ACAT

12

789

BEAN

BEATS

Y

UMBER

C

C

C

C

C

C

C

70

0

3

Bunny

AC

ACAT

12

789

CARROT

BEATS

Y

APRICOT

C

C

C

C

C

C

C

50

0

3

Bunny

AC

ACAT

12

789

CELERY

BEATS

Y

CHOCOLATE

C

C

C

C

C

C

C

80

0

3

Bunny

AC

ACAT

12

789

CORN

BEATS

Y

UMBER

C

C

C

C

C

C

C

55

0

3

Bunny

AC

ACAT

12

789

NONE

POTATO

N

ORANGE

C

C

C

C

C

C

C

65

0

3

Bunny

AC

ACAT

12

789

POTATO

BEATS

Y

APRICOT

C

C

C

C

C

C

C

75

0

3

Bunny

AC

ACAT

12

789

TOMATO

BEATS

Y

C

C

C

C

C

C

C

60

<tbody>
</tbody>

Columns C:G and L:R are all linked to "ID" number.

I would like it to turn out eventually like this below to separate out the "NEW" column and organize the counts but im having trouble splitting it up.

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

ID	NAME	DATA1	DATA2	DATA3	DATA4	INFO1	INFO2	INFO3	INFO4	INFO5	INFO6	INFO7	TOTALCOUNT (ALL OF NEW)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (BEATS ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (CORN ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (POTATO ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (CELERY ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (TOMATO ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (CARROT ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK		TOTALCOUNT (BEAN ONLY)	Y	N	APRICOT	UMBER	ORANGE+CHOCOLATE+BLANK	Y + APRICOT	N + APRICOT	Y + UMBER	N + UMBER	Y + ORANGE+CHOCOLATE+BLANK	N+ORANGE+CHOCOLATE+BLANK
1	Beaver	AA	AAIRPLANE	10	123	A	A	A	A	A	A	A	30	15	15	10	5	15	10	0	5	0	0	15		15	15	0	10	5	0	10	0	5	0	0	0		15	0	15	0	0	15	0	0	0	0	0	15		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0
2	Bear	AB	ABOY	11	456	B	B	B	B	B	B	B	195	195	0	55	35	105	55	0	35	0	105	0		195	195	0	55	35	105	55	0	35	0	105	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0
3	Bunny	AC	ACAT	12	789	C	C	C	C	C	C	C	455	390	65	125	125	205	125	0	125	0	140	65		390	390	0	125	125	140	125	0	125	0	140	0		0	0	0	0	0	0	0	0	0	0	0	0		65	0	65	0	0	65	0	0	0	0	0	65		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0	0	0	0

<tbody>
</tbody>

ALL OF THE TOTALCOUNT (ALL OF NEW) GROUP(N:Y) WILL INCLUDE THE ENTIRE "NEW" LIST. [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ff0000]#ff0000[/URL] ]BEATS, CORN, POTATO, CELERY, TOMATO, CARROT AND BEAN, THEN AFTER IT BREAKS IT UP INTO SEPERATE GROUPS.[/COLOR]

N: ONLY "COUNT" FOR THAT 'ID'
O: ONLY "COUNT" WHEN THE CELL VALUE IS "Y"
P: ONLY "COUNT" WHEN THE CELL VALUE IS "N"
Q: ONLY "COUNT" WHEN THE CELL VALUE IS "APRICOT"
R: ONLY "COUNT" WHEN THE CELL VALUE IS "UMBER"
S: ONLY "COUNT" WHEN THE CELL VALUE IS "ORANGE", "CHOCOLATE" AND "BLANK"
T: ONLY "COUNT" WHEN THE CELL VALUE IS "Y" AND "APRICOT"
U: ONLY "COUNT" WHEN THE CELL VALUE IS "N" AND "APRICOT"
V: ONLY "COUNT" WHEN THE CELL VALUE IS "Y" AND "UMBER"
W: ONLY "COUNT" WHEN THE CELL VALUE IS "N" AND "UMBER"
X: ONLY "COUNT" WHEN THE CELL VALUE IS "Y" AND "ORANGE", "CHOCOLATE" AND "BLANK"
Y: ONLY "COUNT" WHEN THE CELL VALUE IS "N" AND "ORANGE", "CHOCOLATE" AND "BLANK"
Z: BLANK COLOMN TO HELP SEPERATE



Below is the code i have to remove the duplicates and combine some data.
Any help or tips would be greatly appreciated.
Thanks

[FONT=Calibri]Sub CopyColumnToWorkbook()[/FONT]

[FONT=Calibri]Dim sourceColumn As Range, targetcolumn As Range[/FONT]
[FONT=Calibri]Set sourceColumn = Workbooks("dirtydata.csv").Worksheets(1).Columns("B:G")[/FONT]
[FONT=Calibri]Set targetcolumn = Workbooks("cleandata.xlsm").Worksheets(1).Columns("A:F")[/FONT]

[FONT=Calibri]sourceColumn.Copy Destination:=targetcolumn[/FONT]


[FONT=Calibri]ActiveSheet.Range("A1:F10000").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6), Header:=xlYes[/FONT]

[FONT=Calibri]Dim rng As Range, cell As Range[/FONT]

[FONT=Calibri]Set rng = ActiveSheet.UsedRange[/FONT]

[FONT=Calibri]For Each cell In rng.Cells[/FONT]
[FONT=Calibri]cell.Value = UCase(cell.Value)[/FONT]
[FONT=Calibri]Next[/FONT]


[FONT=Calibri]End Sub[/FONT]

 

[mDgo0d4mE]

Would this be even possible to complete, Ive been perusing though the forums and google and cant match what im trying to accomplish?
ive been batting my head at this for the past week now.