• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sum a cell value in one sheet with value in another sheet and post the result in a new consolidated sheet

Amir Wisal

New Member
Hello,

Does anyone have VBA code that I could use in a module to sum a values from the same cell in multiple sheets (i.e. cell B3 in both sheets SDI and 001-SDI) into a final master sheet?

Both the sheets have the exact same text written in Column A (i am not sure whether this will be helpful) but in each sheet there are seven columns with numeric values. The way i want to sum them is to take B2 value from sheet1 (which is SDI in my case) and add B2 value from sheet2 (001-SDI) but i want sum of each cell (cell number will be same i.e B2, B3.... to last entry of the data) from both sheets and in the same manner i want to sum each cell in the row till last filled cell (i.e B2, C2, D2... to H7). it would be great if i somehow get the consolidated data in a different sheet with the sum of each cell mentioned against the text in column A.

For sample i am attaching dummy sheet which has two sheets and the desired results i want, but in reality i have to consolidate more than 20 sheets of data in pairs (although all sheets are name in the same sequence i.e. ABC and 001-ABC and DEF and 001-DEF) and i want the results of both the sheets for every pair in a separate sheet combined i.e "ABC consolidated".

I would be really helpful if the VBA itself recognises the sheets names written in the same pattern and sum them to a master sheet (which will have the consolidated results of those two sheets i.e. either ABC, SDI or DEF) or if the code require me to enter in the names of the sheets (through a dialog box) I want to sum from and the destination master sheet with the final sum.

I am not sure whether i explained it properly but I am very new to VBA so any help would be greatly appreciated!


Thank you.
 

Attachments

Amir Wisal

New Member
Thank you very much Vletm for your help. This is exactly what i wanted. you're a genius.

Column A data is not fixed in my case. Usually in my case column A has data around 100 rows and it would be great if you somehow keep this as dynamic in the code.

I am attaching the actual sheet in which i would perform this. Key differences from my dummy data are listed as follows.
a. This spreadsheet has a lot of sheets in it. the one i want to consolidate are highlighted as green. The problem here is that the sheets are not named correctly this time for example, there are two sheets which i want to consolidate (i.e. IWT and IWT(2)). I want to perform the same operation for the rest of the sheets as well in pair e.g. IGR and IGR(2).
b. The cell A1 in both sheets have the same name i.e. in case of IWT, IWT is mentioned in cell A1 on both IWT and IWT(2).
c. The text in column A is dynamic and might not match on both sheets in some rows (which i can correct through Power Query) but what i need from your end is to sum the values irrespective of the names.
d. Can we please get rid of the msgbox "refreshed".
e. The sequence of both the sheets might not be adjacent to each other. i mean one might be at number 1 other might be at 8 which i want to consolidate.
f. Ignore all other sheets which does not have the 2nd pair.
g. FYI - There are some other macros in the sample file i am attaching and first sheet from the right (i.e. C1233) is fetching data from Power Query which is the source file.
h. Can we please somehow create a new sheet each time we consolidate two sheets and give them a proper name like in case of IWT and IWT(2) can we get the consolidated result on a new sheet called "IWT consolidated".

I know i am asking for too much but would really appreciate if you could help me on this.

Thank you so much once again.
 

Attachments

Last edited:

vletm

Excel Ninja
Amir Wisal
Your sample file should has been as Your real file.
... Your original sample file ... how do You think?

Fixed A-column means for me that those rows are in same in every sheet eg A2's value is IDS in every sheet - no matter number of rows.
If not in same order then ... that could be possible too ... but needs more code.
If You now want to calculate only green-sheets, that could be possible.
Dynamic -- dynamic -- dynamic ... hmm?
About Your ...
d) "ReFreshed" is there now, to show that results are refreshed ... it could take time and in many cases ... the results should be ready at once ...
e) ... before I would do any changes, there cannot be any 'might be' - there should be clear rules.
f) ... Ignore all other sheets which does not have the 2nd pair. ... hmm? 1st pair ... 2nd pair ... 3rd pair?
g) I check only my codes.

Asking ...
You wrote a lot
- to which sheets, now should show the result.
- clear rules
- expected results
- if result-sheet has something, which should stay there - You should name those too.
 

Amir Wisal

New Member
Thank you Vletm for bearing with me.

clear rules are as follows.

a. For each pair of sheets, cell A1 contains the same name e.g. for both IDS sheets i.e IDS and IDS(2), A1 will show IDS. same is the case with all other sheets.
b. For each pair of sheets, i want to show consolidated results in a separate sheet. e.g. for IDS, i want your code to create a new sheet and pasted the consolidated figures. same goes for CAE and all others.
c. Refreshed dialogue is fine. this way i would know that the data is refreshed.
d. For the code, i will manually rearrange all sheets in the same order as provided in the sample sheet (attached below). so we'll not worry about the sequence. i will keep all the pair close to each other before running the code.
e. I have removed all other sheets which does not need consolidation.
f. Expected results would be to consolidate each pair of sheets i.e. IDS and IDS(2) in a separate sheet named "IDS Consolidated" from B2 to the last cell of data. same goes for all other sheets. steps will be;
1) search for names mentioned in A1 and consolidate sheets based on it. e.g. if a A1 has IDS in it, code will lookup all the sheets which has IDS mentioned in A1 (which will be 2 sheets each)
2) Add the values of each cells based on the criteria in A1. e.g. for IDS it needs to sum sheets with IDS in A1
3) Create a new sheet name "IDS consolidated" and paste the resultant figures for IDS in that. same step for all other sheets.
g. Result sheet will now have the names mentioned in column A and the sum of each cell value in both sheets - just like you did with the dummy data. In each consolidated sheet, column A will be text and rest of the 7 columns will be numeric values (which obviously will be sum of each cell in those two sheets)

Hope i am clear with the instructions this time around.

Again, appreciate you take out your valuable time helping me. means a lot
 

Attachments

vletm

Excel Ninja
Amir Wisal
Why ... do You files vary every time?
Is that file as Your normal used file?
... if no then - why different file?
... that means double work even for You.
a) Where do need those A1-values?
b) How many consolidated sheets there should be?
d) Why do You do something manually? What matters of order of sheets?
e) Why? ... soon You could do everything manually too.
f) ... steps will be ... hmm?
If You know what to do - then why You don't do it Your way?
If You expect specific Your steps then ... someone else would do those.
I need sample of expected results - You tried to write ... write ... write.
 

Amir Wisal

New Member
Hi Vletm,

Thank you so much. I kept on changing the file every time.

i just need two final amendments with the VBA that you've shared in the first reply using my dummy data. forget all other conversation in between.

1. Can we fetch the text in column A to the desired results sheet as currently it is summing values only (text in both sheets will be same).
2. Can i get to choose which sheets i want to sum up in the desired results sheets? I mean if a dialogue box opens where i can enter the sheets names i want to add or just click on the sheets i want to sum up in the desired results sheets.

Adding dummy sheet again for your reference which contains the original code shared by yourself.

Thank you again for your effort and support.
 

Attachments

vletm

Excel Ninja
Amir Wisal
Did You skip my writings?
Did You test You above writing with Your the 1st file?
Why do You would like to do so much manually?

For my sample - I'll use Your #5 reply file.
#1 For some reason, You manually did almost all sheets A-column same ... please, next time use Your original datas.
#2 I added MAIN-sheet
#3 [ Sheets ]-button refresh sheets-name
#4 Select wanted sheets by selecting those one by one - green is selected
#5 Press [ Do It ]
#6 You will get results in MAIN-sheet
... hmm?
> After, You've original data in those A-columns - I could continue.
 

Attachments

Amir Wisal

New Member
Hello Vletm,

I don't know how to thank you. you're a genius. Because good people like you exists we newbies to VBA get inspired.

I am sorry for keep changing the data and not answering your questions specifically but thanks again that solves my problem.

I will keep playing with the code that you've provided and in future, if i face any issue i will get back to you.

Many thanks and A big thumbs up :)
 

vletm

Excel Ninja
It's still a sample - not ready for normal use.

Because I couldn't activate You ...

I tested some features...

> D5 shows those selected sheets

> no need to worry are MAIN's D-column same as other sheet's A-column
... if different values then only same values will paste (otherway D-column shows yellow and that sheet in comment)

> [ MODIFY ]
> below that are all possible D-column values ...
> if select [ MODIFY ] to green
... then it's possible to RESET D-column
... then it's possible to add more values to D-column by selecting below values
... then it's possible to delete values to M-column by selecting D-column values
>> Remember to select [ MODIFY ] after above to 'blank'-color <<

... hmm? ... if MAIN-sheet would need to get out with PDF ... that's possible to code too.

There could be something else too
... You use ... You should know.

Amir Wisal
Ps ... I updated file
 

Attachments

Last edited:
Top