VBA code for sum with criteria through several sheets

T

TheLSD

New Member
Joined
Jan 12, 2022
Messages
33
Office Version
  1. 2010
Platform
  1. Windows
Hi
So I have a template of Recap for summing several sheets into one sheet only. The problem is that the data sheets are not the same as the template, each only includes the needed code, but the code between the recap sheet and with datasheets are all the same. Usually, in some rare cases sooner, every three months, there're new codes added. Each divisions that submits the data to me, sometimes, delete some of the code in a section (the example on HR worksheet).

The code I use right now is this

VBA Code: 
'612101 OPS-Salaries
Sheets(2).Range("E12:P12").Formula = "=Sum('ACF:TSD'!R[0]C[0])"

for each code, but when I need to add new code, may as well need to change all the range from up to down to the last depending how many rows inserted

What I need is VBA code to sum based on the code through the sheets on the workbook, with dynamic range so that I don't need to change the range when a new code is inserted, is it possible?

for the sheet amount, usually, I take about 16 or less but I make it ACF sheet as the first and TSD as the last cause both always submit to me and both are the beginning and the end of the datasheets
Here's some part of the Recap sheet

CODEDESCRIPTION Total JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
600000Operational Cost
610000OPS-Employee Compensation
612100OPS-Employee Compensation
612101OPS-Salaries-
612102OPS-Wages-
612103OPS-Transport-
612104OPS-Medical-
612105OPS-Hospital-
612106OPS-Incentive-
612107OPS-HP-
612108OPS-driver allowance-
612109OPS-Overtime-
612110OPS-Bonus-
612111OPS-Consumption Allowance-
612112OPS-Period of Employee Benefits-
612113OPS-Severance-
612114OPS-Glasess Allowance-
612199OPS-Other Allowance-
-
SUB TOTAL EMPLOYEE COMPENSATION *-------------
612200OPS-ASTEK & Pension Funds
612201OPS-A s t e k-
612202OPS-Pension-
612203OPS-Bonuses-
612204OPS-Long Service(PEB)-
612205OPS-Other-
SUB TOTAL ASTEK & PENSION FUND-------------
612300OPS-Employee Welfare
612301OPS-Cafetaria-
612302OPS-Recreation-
612303OPS-Uniform-
612304OPS-Marriage Allowance-
612305OPS-Home Rent Allowance-
612306OPS-Medical-
612307OPS-others-
612308OPS-Hospital-
612309OPS-Sport Allowance-
612310OPS-Equipment allowance-
612311OPS-Transport-
612312OPS-Handphone allowance-
612399OPS-Employee Welfare Others-
SUB TOTAL EMPLOYEE WELFARE-------------
613000OPS-Commision Expense
613100OPS-Advertising & Promotion
613101OPS-Advertising-
613102OPS-Promotion Event-
613103OPS-Material Promotion-
613303OPS-MATERIAL PROMOTION MERCHANDISE(JAKET,PAYUNG,GANTUNGAN KU-
613104OPS-Sponsorship-
613105OPS-Voucher-
613106OPS-Product Sample-
613107OPS-Tours-
613108OPS-Service Campaign-
613304OPS-SERVICE CAMPAIGN(DIRECT GIFT,DEALER CONTEST-
613109OPS-Road Test-
613110OPS-Training-
613302OPS-Training-
613111OPS-Sales Expense Unit-
613301OPS-SALES EXPENSE-
613112OPS-FEE/JASA-
613113OPS-Material Promotion (New)-
613114OPS- New 1-
613115OPS- New 2-
613199OPS-Adv & Prom Others-
613399OPS-ADV & PROM OTHERS-
SUB TOTAL ADVERTISING & PROMOTION *-------------
700000Non Operational Expense
710000NON OPS-Employee Compensation
712100NON OPS-Employee Compensation
712101NON OPS-Salaries-
712102NON OPS-Wages-
712103NON OPS-Transport-
712104NON OPS-Medical-
712105NON OPS-Hospital-
712106NON OPS-Incentive-
712107NON OPS-HP-
712108NON OPS-Driver Allowance-
712109NON OPS-Overtime-
712110NON OPS-Bonus-
712111NON OPS-Meal Allowance-
712112NON OPS-Period of Employee Benefits-
712113NON OPS-Severance-
712114NON OPS-Glasess Allowance-
712115NON OPS-THR-
712116NON OPS-Operational Messenger & Collector-
712117NON OPS-non sales incentive-
712118NON OPS-Placement-
712119NON OPS-COP-
712120NON OPS-MOP-
712121NON OPS-Tax Allowance-
712199NON OPS-Other Allowance-
SUB TOTAL NON OPS EMPLOYEE COMPENSATION-------------
712200NON OPS-ASTEK & Pension Funds
712201NON OPS-A s t e k-
712202NON OPS-Pension-
712203NON OPS-Payment-
712204NON OPS-Long Service-
712205NON OPS-Insurance-
712206NON OPS-Health Insurance-
712207NON OPS-Severance-
SUB TOTAL NON OPS ASTEK & PENSION FUNDS-------------
712300NON OPS-Employee Welfare
712301NON OPS-Cafetaria-
712302NON OPS-Recreation-
712303NON OPS-Uniform-
712304NON OPS-Marriage Allowance-
712305NON OPS-Home Rent Allowance-
712306NON OPS-Medical-
712307NON OPS-Accident-
712308NON OPS-Hospital-
712309NON OPS-Sport Allowance-
712310NON OPS-Equipment allowance-
712311NON OPS-Transport-
712312NON OPS-Phone allowance-
712399NON OPS-Employee Welfare Others-
SUB TOTAL NON OPS EMPLOYEE WELFARE-------------
712400NON OPS-Training & Education
712401NON OPS-Training & Education-
712402NON OPS-FEE/JASA-
SUB TOTAL NON OPS TRAINING & EDUCATION-------------
712500NON OPS-Representation & Entertainment
712501NON OPS-Representation-
712502NON OPS-Entertaiment-
SUB TOTAL NON OPS REPRESENTATION & ENTERTAINMENT-------------


Example of datasheet
Data from HR

CODEDESCRIPTION Total JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
600000Operational Cost
610000OPS-Employee Compensation
612100OPS-Employee Compensation
612101OPS-Salaries240.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.000
612102OPS-Wages-
612103OPS-Transport-
612104OPS-Medical-
612105OPS-Hospital-
612106OPS-Incentive-
612107OPS-HP-
612108OPS-driver allowance-
612109OPS-Overtime-
612110OPS-Bonus-
612111OPS-Consumption Allowance-
612112OPS-Period of Employee Benefits-
612113OPS-Severance-
612114OPS-Glasess Allowance-
612199OPS-Other Allowance-
-
SUB TOTAL EMPLOYEE COMPENSATION *240.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.00020.000.000
612200OPS-ASTEK & Pension Funds
612201OPS-A s t e k-
612202OPS-Pension12.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.0001.000.000
612203OPS-Bonuses9.000.000750.000750.000750.000750.000750.000750.000750.000750.000750.000750.000750.000750.000
612204OPS-Long Service(PEB)-
612205OPS-Other-
SUB TOTAL ASTEK & PENSION FUND21.000.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.0001.750.000
700000Non Operational Expense
710000NON OPS-Employee Compensation
712100NON OPS-Employee Compensation
712101NON OPS-Salaries72.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.0006.000.000
712102NON OPS-Wages54.000.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.000
712103NON OPS-Transport-
712104NON OPS-Medical-
712105NON OPS-Hospital-
712106NON OPS-Incentive-
712107NON OPS-HP-
712108NON OPS-Driver Allowance-
712109NON OPS-Overtime-
712110NON OPS-Bonus-
712120NON OPS-MOP-
712121NON OPS-Tax Allowance-
712199NON OPS-Other Allowance-
SUB TOTAL NON OPS EMPLOYEE COMPENSATION54.000.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.0004.500.000
712200NON OPS-ASTEK & Pension Funds
712201NON OPS-A s t e k-
712202NON OPS-Pension2.930.000150.000680.000500.000600.0001.000.000
712203NON OPS-Payment-
712204NON OPS-Long Service-
712205NON OPS-Insurance-
712206NON OPS-Health Insurance-
712207NON OPS-Severance-
SUB TOTAL NON OPS ASTEK & PENSION FUNDS2.930.000150.000--680.000-500.000--600.000--1.000.000
712400NON OPS-Training & Education
712401NON OPS-Training & Education9.000.0001.500.0001.500.0001.500.0001.500.0001.500.0001.500.000
712402NON OPS-FEE/JASA-
SUB TOTAL NON OPS TRAINING & EDUCATION9.000.0001.500.000-1.500.000-1.500.000-1.500.000-1.500.000-1.500.000-
712500NON OPS-Representation & Entertainment
712501NON OPS-Representation-
712502NON OPS-Entertaiment1.450.000250.000500.000700.000
SUB TOTAL NON OPS REPRESENTATION & ENTERTAINMENT1.450.000250.000----500.000---700.000--
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
below some other examples of other datasheet

the yellow cells are the example of adding new rows

CODEDESCRIPTION Total JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
613000OPS-Commision Expense
613100OPS-Advertising & Promotion
613101OPS-Advertising18.040.0002.000.0003.000.000250.000900.0001.500.000890.000600.000800.000450.000250.0001.000.0006.400.000
613102OPS-Promotion Event-
613103OPS-Material Promotion-
613303OPS-MATERIAL PROMOTION MERCHANDISE(JAKET,PAYUNG,GANTUNGAN KU5.000.0005.000.000
613104OPS-Sponsorship1.450.000650.000800.000
613105OPS-Voucher8.900.0008.900.000
613106OPS-Product Sample3.750.0002.500.000750.000500.000
613107OPS-Tours-
613108OPS-Service Campaign-
613304OPS-SERVICE CAMPAIGN(DIRECT GIFT,DEALER CONTEST-
613109OPS-Road Test-
613110OPS-Training2.625.00045.000600.0001.500.000480.000
613302OPS-Training-
613111OPS-Sales Expense Unit-
613301OPS-SALES EXPENSE7.000.0007.000.000
613112OPS-FEE-
613113OPS-Material Promotion (New)-
613114OPS- New 1-
613115OPS- New 2-
613199OPS-Adv & Prom Others-
613399OPS-ADV & PROM OTHERS-
SUB TOTAL ADVERTISING & PROMOTION *28.725.0007.045.00013.900.000-3.100.000-650.0001.500.000800.000750.000480.000-500.000
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
VBA Sum formula... adjust dynamically
Replies
4
Views
540
JLGWhiz
JLGWhiz
P
  • Question Question
VBA to create proposal sheet(s)
Replies
3
Views
1K
padadof2
P
C
  • Question Question
Excel VBA to sum 2 fields based on multiple criteria and then copy some of the fields to a new sheet
Replies
8
Views
1K
chillinsf49
C
B
  • Question Question
HELP - Advanced Userform 3 level dependent drop down
Replies
1
Views
2K
MickG
M
R
  • Question Question
vlookup help
Replies
0
Views
1K
Roosterfosho
R

Forum statistics

Threads
1,224,827
Messages
6,181,197
Members
453,021
Latest member
pingpong7117

Share this page

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