macro insert three columns repeatedly with all values based on multiple sheets

KalilMe

Active Member
Joined
Mar 5, 2021
Messages
354
Office Version
  1. 2016
Platform
  1. Windows
hi
I have many sheets contain data and values for sales and purchase
in sheet report has also data . what I want insert three columns with the same borders and formatting contain (PURCHASE,SALES,BALANCE) and summing the values for SALES , PURCHASE across multiple sheets and put the values in sheet REPORT in COLUMN SALES & PURCHASE . the COL BALANCE should add the formula as I put in sheet CASE1 when run the macro from the first time but when I run again . the formula will be change as I put in COL BALANCE in sheet CASE2 and somtimes there are some a new items in all the sheets except the sheet REPORT then should add it as I highlighted in sheet REPORT . when get the values after summing the values across sheets should match COL B,C,D toghether and when add a new items in sheet REPORT should match COL A with the others sheets in COL A
NOTE: the sheet CASE1,2 just to understand what I want . the result should be in sheet REPORT and the formula in CASE2 it will continue every time run the macro .
also issued in this forum macro insert three columns repeatedly with all values based on multiple sheets

first sheet

COLLECTION.xlsm
ABCDEF
1CODEBRTYORPURCHASESALES
2FR-00FRBANANATT20010
3FRAPPLELL10020
4FRPEARNN10-
5FRBANANAQQ20-
6VEG-00VEGTOMATOSS12-
7VEGTOMATOAA1212
8FO-00TUNA180GTH205
9TUNA180GIND10
SAS



second sheet

COLLECTION.xlsm
ABCDE
1CODEBRTYORSALES
2FR-00FRBANANATT5
3FRAPPLELL2
4FRPEARNN3
5FRBANANAQQ-
6VEG-00VEGTOMATOSS5
SS


third sheet
COLLECTION.xlsm
ABCDE
1CODEBRTYORPURCHASE
2FR-00FRAPPLELL120
3FRPEARNN30
4FRBANANAQQ40
5VEG-00VEGTOMATOSS50
6VEGTOMATOAA5
7VEGONIONAA16
8VEGPOTATOAA27
SR


before run the macro how was it in this sheet
COLLECTION.xlsm
ABCD
1
2CODEBRTYOR
3FR-00FRBANANATT
4FRAPPLELL
5FRPEARNN
6FRBANANAQQ
7TOT
8VEG-00VEGTOMATOSS
9VEGTOMATOAA
10VEGONIONAA1
11TOT
REPORT



the expected result in sheet REPORT for two cases
case1
COLLECTION.xlsm
ABCDEFG
1JAN
2CODEBRTYORPURCHASESALESBALANCE
3FR-00FRBANANATT32015305
4FRAPPLELL1002278
5FRPEARNN40337
6FRBANANAQQ60-
7TOT52040420
8VEG-00VEGTOMATOSS67562
9VEGTOMATOAA17125
10VEGONIONAA16-6
11VEGPOTATOAA27-7
12TOT971780
13FO-00TUNA180GTH20515
14TUNA180GIND10010
15TOT30525
CASE1
Cell Formulas
RangeFormula
G13:G14,G8:G11,G3:G5G3=E3-F3
E12:G12,E7:G7E7=SUM(E3:E6)
E15:G15E15=SUM(E13:E14)

case 2
COLLECTION.xlsm
ABCDEFGHIJ
1JANFEB
2CODEBRTYORPURCHASESALESBALANCEPURCHASESALESBALANCE
3FR-00FRBANANATT3201530532015610
4FRAPPLELL100227810022156
5FRPEARNN4033740374
6FRBANANAQQ60-6060-120
7TOT5204048052040960
8VEG-00VEGTOMATOSS67562675124
9VEGTOMATOAA17125171210
10VEGONIONAA16-66-12
11VEGPOTATOAA27-77-14
12TOT9717809717160
13FO-00TUNA180GTH2051520530
14TUNA180GIND1001010020
15TOT3052530550
CASE2
Cell Formulas
RangeFormula
E12:J12,E7:J7E7=SUM(E3:E6)
G13:G14,G8:G11,G3:G6G3=E3-F3
J13:J14,J8:J11,J3:J6J3=G3+H3-I3
E15:J15E15=SUM(E13:E14)

I hope finding answer in this forum despite this project is very coplicated
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Forum statistics

Threads
1,223,275
Messages
6,171,123
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top