Consolidated data from 3 worksheets and populate to another sheets

[uky]

Hi All,

I have one workbook contains multiple worksheets.
Sheets database as database all transaction, sheets S7 as database limit and sheet S6 as counterparty name.

I already have macro code, but it's run slow. Any one could help me with this case.


Edit Post:

I need to store data in array an populate to sheet.
Example:
Sheet "database":
Ctg | utilisasi | BNB |
A | 20 | Y |
B | 10 | N |
A | 5 | Y |
A | 25 | Y |
B | 2 | N |
C | 100 | Y |
D | 22 | N |
D | 10 | N |

Sheet "name":
LBL | Full |
A | Andaro |
B | Bandada |
C | Calamanta |
D | Delemi |

Sheet "Limit":
Code | Limit | BNB1 |
A | 200 | Y
B | 300 | N
C | 100 | Y
D | 50 | N

Result that i need:

Sheet "1":
CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
A | Andaro | 200 | 50 | 150
C | Calamanta | 100 | 100 | 0

Sheet "2":
CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
B | Bandada | 300 | 12 | 288
D | Delemi | 50 | 32 | 18

Thank you,

[uky]

Hi Admin,

Here with my code:

Please Login or Register  to view this content.

macro ins_lim:

Please Login or Register  to view this content.

Continue to below post

[uky]

macro ins_uti:

Please Login or Register  to view this content.

macro ins_convert:

Please Login or Register  to view this content.

[uky]

macro ins_uti:

Please Login or Register  to view this content.

[uky]

macro ins_convert:

Please Login or Register  to view this content.

[uky]

Hi! Anyone can help with my code?
So my codes simple and fast.

[AB33]

UKY,
No, you code is very long and diffcult to follow it. Jerry has also asked you to include you code with the attached, but instead you have almost filled in an enitre page with your code.
Please explain what are you trying to do and people may be able to help.

[JBeaucaire]

Most of the "tricks" I know for speeding up most people's code, you've got that already. It appears you running "loops", 6000 loops inside of 6000 loops. Within that looping you're writing to and from the sheet. All of that activity takes a alot of time.

[uky]

Hi Ab and Jerry,

Thanks for your respons.
I attached again file with macro included.
And AB, your codes is part of my codes now(previous macro that i ask to forum..:D)
Here with my file: icon2.jpg

[JBeaucaire]

Without referring to VBA, can you tell us verbally what is being accomplished by this macro?

[uky]

Hi Jerry,

Sheet “1”:
1. Filter in sheets database with column AD = “Y”
2. Data Column C from column J[ sheets database] and sum column AB
3. Data Column E from column C[sheet S7].
4. Data Column F from column D[sheet S7].
5. Data Column G from column E[sheet S7].
6. Data Column H from column I[sheet S7].
7. Data Column K from column J[sheet S7].
Sheet “2”:
1. Filter in sheets database with column AD = “N”
2. Data Column C from column J[ sheets database] and sum column AB
3. Data Column E from column C[sheet S7].
4. Data Column F from column D[sheet S7].
5. Data Column G from column E[sheet S7].
6. Data Column H from column I[sheet S7].
7. Data Column K from column J[sheet S7].

Hope this clarify what i need.

[JBeaucaire]

I understand what 1. means. I don't understand what results / where results are derived from 2-7.

[uky]

Hi Jerry,

Sheet 1:
1. Filter in sheets database with column AD = “Y”
2. Value in Column C sheets(1) from column J[ sheets database] and sum column AB(sheets database)
3. Value in Column D sheets(1) from column D[ sheets S6]
4. Value in Column E sheets(1) from column C[sheet S7] = "FX" and column K[sheets S7] = "Y".
5. Value in Column F sheets(1) from column D[sheet S7].
6. Value in Column G from column E[sheet S7].
7. Value in Column H from column I[sheet S7].
8. Value in Column I from sum column AB(sheets database)
9. Data Column K from column J[sheet S7].

Sheet 2:
1. Filter in sheets database with column AD = “N”
2. Value in Column C sheets(1) from column J[ sheets database] and sum column AB(sheets database)
3. Value in Column D sheets(1) from column D[ sheets S6]
4. Value in Column E sheets(1) from column C[sheet S7] = "FX" and column K[sheets S7] = "N".
5. Value in Column F sheets(1) from column D[sheet S7].
6. Value in Column G from column E[sheet S7].
7. Value in Column H from column I[sheet S7].
8. Value in Column I from sum column AB(sheets database)
9. Data Column K from column J[sheet S7].

This is it jerry.

Thank you.

[abousetta]

Without trying to rewrite your code since you obviously understand it better than I do, and have shown the ability to deal with arrays, my suggestions would be the following:

1) Use With... End With instead of hardcoding sheet identifiers. I have read that this speeds up the code because it stores the sheet (or other object identifier) in memory.

2) Instead of printing to one cell at a time, convert the range that wil be manipulated to an array and make the changes to the array. Then only in the end of the subroutine, print the full array to the worksheet. Repeated writing of cells takes time even if screenupdating is turned off.

3) If the subs are running independently then there are ways to trick Excel into running them simulatenously even though vba does not support multithreading.

Hope this helps.

abousetta

[uky]

Hi abousetta,

Thank's for your suggestion. But i thing, i need don't need to loop entire sheets.
I need to store data in array an populate to sheet.
Example:
Sheet "database":
Ctg | utilisasi | BNB |
A | 20 | Y |
B | 10 | N |
A | 5 | Y |
A | 25 | Y |
B | 2 | N |
C | 100 | Y |
D | 22 | N |
D | 10 | N |

Sheet "name":
LBL | Full |
A | Andaro |
B | Bandada |
C | Calamanta |
D | Delemi |

Sheet "Limit":
Code | Limit | BNB1 |
A | 200 | Y
B | 300 | N
C | 100 | Y
D | 50 | N

Result that i need:

Sheet "1":
CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
A | Andaro | 200 | 50 | 150
C | Calamanta | 100 | 100 | 0

Sheet "2":
CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
B | Bandada | 300 | 12 | 288
D | Delemi | 50 | 32 | 18

[uky]

Hi Jerry,

Need your assistant.

Thank you

[tigeravatar]

uky,

Attached is an example workbook based on the sample data and expected output you provided.
It contains the following macro:

Please Login or Register  to view this content.

Running that macro will pull the data and give the expected outputs on the appropriate sheets. Hopefully you can adapt it to suit your actual workbook.

[uky]

Hi Tigeravatar,

Thank you for your help and it's work perfect.
I'm trying to understand your codes. but I still do not understand much.
Could you give comments next line of your code?
This code, need comment:

Please Login or Register  to view this content.

[tigeravatar]

That line of code ensures that it only pulls data the first time a new CTG is encountered in column A. In the sample data there are duplicates ("A" is shown three times, "B" is listed twice, etc), so in order to increase efficiency and accuracy, that line prevents the code from running the same data more than once.

[uky]

That line of code ensures that it only pulls data the first time a new CTG is encountered in column A. In the sample data there are duplicates ("A" is shown three times, "B" is listed twice, etc), so in order to increase efficiency and accuracy, that line prevents the code from running the same data more than once.

OK Thank you very much Tigeravatar. My case closed.