Sum a cell value in one sheet with value in another sheet

[Amir Wisal]

Hello to the best community i encountered ever,

I am just starting to learn VBA to do some analyses and to make my tasks way much easier.

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 sharing two sheets, 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.

VBA Dummy Data.xlsx
	A	B	C	D	E	F	G	H
1	001-SDI
2	External	26,051	44,625	45,891	8,575	44,260	39,419	10,744
3	Interdivisional	20,485	15,518	46,578	39,205	34,559	20,559	27,669
4	Intergroup	11,887	21,841	11,228	20,374	33,636	42,612	17,957
5	DIRECT EXPENSES	12,112	47,475	31,819	13,095	14,203	37,305	11,772
001-SDI

VBA Dummy Data.xlsx
	A	B	C	D	E	F	G	H
1	SDI
2	External	31557	6030	29440	45989	24081	47581	38651
3	Interdivisional	22471	33005	49279	20185	7727	26411	19864
4	Intergroup	40263	48854	12859	12784	23412	29008	25697
5	DIRECT EXPENSES	9564	33914	16079	40553	9346	31990	11418
SDI

VBA Dummy Data.xlsx
	A	B	C	D	E	F	G	H
1	SDI Consolidated
2	External	57608	50655	75331	54564	68341	87000	49395
3	Interdivisional	42956	48523	95857	59390	42286	46970	47533
4	Intergroup	52150	70695	24087	33158	57048	71620	43654
5	DIRECT EXPENSES	21676	81389	47898	53648	23549	69295	23190
Desired results

Cell Formulas
Range	Formula
B2:H5	B2	='001-SDI'!B2+SDI!B2

 

[Amir Wisal]

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

Hi All,

Some one has shared the below code which is working fine on the dummy data i shared above however in practical i have to use it on the below workbook. Can someone modify this code for me to suit my requirements. This code works fine if Column A data is fixed which is not 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.

Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
t_sh = "Desired results"
With Sheets(t_sh)
y = .UsedRange.Rows(.UsedRange.Rows.Count).Row
x = .UsedRange.Columns(.UsedRange.Columns.Count).Column
If x < 2 Then x = 2
.Range(.Cells(1, 2), .Cells(y, x)).ClearContents
End With
For sh = 1 To Sheets.Count
If Sheets(sh).Name <> t_sh Then
Sheets(sh).UsedRange.Copy
Sheets(t_sh).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:=False, Transpose:=False
End If
Next sh
Application.ScreenUpdating = False
MsgBox "Refreshed", vbInformation, "Note"
End Sub

Key differences between the dummy data and the actual data are as follows.

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".

File can be accessed from the link below.

C1233 PQ with 97-2003 - Div wise Final.xlsm

Column (2) Column1,Column3,Column5,Column8,Column10,Column12,Column15,Column17 IDS External,13330730,15218144,-1887414,89796865,134888097,-45091232,180542529 Interdivisional,0,0,0,221785,0,221785,0 Intergroup,160172,98877,61295,2792743,876412,1916331,1173044 Total gross revenue,13490902,15317021...

docs.google.com

Thank you