code vba collection data from multible sheets to sheet1

0

i have sheet1,2,3,4  contains data i'd like transfer data to sheet5  but show the total every brand with formula column(d ) like this : sheet5(d)= (sheet1(d)+sheet2(d)+sheet4(d))-sheet3(d)

Answer
Discuss

Answers

0
Selected Answer

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

Discuss

Discussion

thanks  so much for your advices and your answering  i just ask about code to paracticing 
leopard (rep: 6) Jul 17, '19 at 2:27 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login