code vba collection data from multible sheets to sheet1

Replace my semi colons with a comma to make the formulas work.

I've attached a file with 2 examples.

My Q to you: why VBA if Excel on itself can do the job?

There are several ways to do this. First what I noticed is that the numbers in your sheets (column balance) are stored as numbers and text (numbers are alligned to the right / Text to the left). You can solve this by selecting the column and then after that go to the Data tab, Text to Columns and click finish.

Furthermore, I don't understand why you are using several tabs for the same data. My advice, use 1 sheet with all the data. It makes your life much easier.

Then make a table of your data (select a cell a go to Insert >> Table (advice: give your table a name; not mandatory but again it makes your life easier)

The benefit of a table is that it makes your range dynamic.

After this is done, you can make use of a pivot table (example 2) or working with formulas (example 1). The benefit of example 1 is that it will always update your balance. A Pivot Table needs to be refreshed after adding new data.

The formula I used is with the function SUMIFS (and I gave every column of the table a separate name.

=SUMIFS(balance;code_number;G14)

If you have never worked with tables, this is a short introduction of an article I have written

https://excelxl.nl/nieuws/why-you-should-convert-almost-every-data-into-a-table-in-excel/

Cheers