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

mDgo0d4mE

New Member
Joined
Oct 9, 2017
Messages
9
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
DATA1DATA2DATA3DATA4OLDNEWY OR NTYPEINFO1
INFO2INFO3INFO4INFO5INFO6INFO7COUNT
01Beaver
AAAAIRPLANE10123BEANBEATSYUMBERAAAAAAA5
01BeaverAAAAIRPLANE10123CORNBEATSYAPRICOTAAAAAAA10
01BeaverAAAAIRPLANE
10
123
NONECORNNORANGEAAAAAAA15
02BearABABOY11456BEANBEATSYCHOCOLATEBBBBBBB40
02BearABABOY11456CARROTBEATS
YORANGEBBBBBBB20
02BearABABOY11456CORNBEATSYAPRICOTBBBBBBB25
02BearABABOY11456NONEBEATSYUMBERBBBBBBB35
02BearABABOY11456POTATOBEATSY
BBBBBBB45
02BearABABOY11456TOMATOBEATSYAPRICOTBBBBBBB30
03BunnyACACAT12789BEANBEATSYUMBERCCCCCCC70
03BunnyACACAT12789CARROTBEATSYAPRICOTCCCCCCC50
03BunnyACACAT12789CELERYBEATSYCHOCOLATECCCCCCC80
03BunnyACACAT12789CORNBEATSYUMBERCCCCCCC55
03BunnyACACAT12789NONEPOTATONORANGECCCCCCC65
03BunnyACACAT12789POTATOBEATSYAPRICOTCCCCCCC75
03BunnyACACAT12789TOMATOBEATSY
CCCCCCC60

<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
NAMEDATA1
DATA2DATA3DATA4INFO1INFO2INFO3INFO4INFO5INFO6INFO7TOTALCOUNT (ALL OF NEW)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (BEATS ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (CORN ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (POTATO ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (CELERY ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (TOMATO ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (CARROT ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
TOTALCOUNT (BEAN ONLY)YNAPRICOTUMBERORANGE+CHOCOLATE+BLANKY + APRICOTN + APRICOTY + UMBERN + UMBERY + ORANGE+CHOCOLATE+BLANKN+ORANGE+CHOCOLATE+BLANK
1BeaverAAAAIRPLANE10123AAAAAAA3015151051510050015
1515010501005000
1501500150000015
000000000000
000000000000
000000000000
000000000000
000000000000
2BearABABOY11456BBBBBBB195195055351055503501050
195195055351055503501050
000000000000
000000000000
000000000000
000000000000
000000000000
000000000000
3BunnyACACAT12789CCCCCCC455390651251252051250125014065
3903900125125140125012501400
000000000000
6506500650000065
000000000000
000000000000
000000000000
000000000000

<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

Code:
[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]
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

mDgo0d4mE

New Member
Joined
Oct 9, 2017
Messages
9
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,029
Messages
5,639,624
Members
417,101
Latest member
amoverton2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top