vba to copy data from one workbook to another workbook based on set of conditions

sanyo1234

New Member
Joined
May 17, 2016
Messages
37
hi guys
I am struggling to get the data from one source WB to another destination new WB based on selection criteria with VBA.
the source WB is as follows
col1 col2 col3 col4
desc center receipt payment
AA 12 1 3
BB 13 2 4
AA 12 3 5
AA 13 6 7
AA 12 7 8
BB 12 1 2
AA 15 2 4
BB 10 1 5
BB 13 1 2

if we specify to get AA desc only it should be in new WB as with auto sum of receipt and payment and sorted ascending at "center"

desc center receipts payment
AA 12 11 16
AA 13 6 7
AA 15 2 4

if we specify to get BB desc only it should be in new WB as with auto sum of receipt and payment and sorted ascending at "center"
desc center receipts payment
BB 10 1 5
BB 12 1 2
BB 13 3 6

I am VERY thankful for your support.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
desccentrereceiptpaymentPRETEND NEW WORKBOOK > > >
AA1213descBB
BB1324
AA1235Count of centre
AA1367centreTotal
AA1278101
BB1212121
AA1524132
BB1015Grand Total4
BB1312
centrereceiptpayment
1015
1212
1336
an intermediate pivot table to take your selection of desc
and then a simple sumproduct equation
eg the first receipt =1
=SUMPRODUCT(($A$2:$A$10=$L$2)*($B$2:$B$10=$K14)*($C$2:$C$10))
this would clearly be of the form
=sumproduct(([workbook1]sheet1!$A$2………………………

<colgroup><col span="10"><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

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
Back
Top