Avatar of david matthews
david matthews
 asked on

I need to combine data from 13 sheets in a running total as a VBA and not a pivot table.

Hi I have a workbook of which has 13 sheets and I want to keep a running total for all sheets.

Ive been looking on internet and was looking at pivot tables  and asked on here but it go so confusing and over the top of my head so looking at macros at believe this will sort it too.   Don't know where to start as not that far advanced.  Let me explain what I need to do.

In sheets 1-13 the layout will be the same as in column headings etc.

in the sheet called "report"  I need it to show a running total in the columns  from sheets 1-13 as follows..

If in sheets 1-13 in column B shows the word "CUBIC" , "SHERE" "CLB" "UNDER £1"  I need the data from columns B,C,D,F carried over into a running total on the sheet called report.

If in sheets 1-13 in column B it does not show the words as shown above I need the data from columns B & F copied over into a running total in the sheet called report.

If it means doing it in 2 separate tables that's OK as long as they are both on the sheet called report.

I know that I will need a macro to update the data from all sheets when I click save or close workbook and thats fine.

Please find enclosed copy of worksheet.
test-wednesday.xlsm
VBA* Excel templates

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

The workbook you attached is not much use since there's no data and the CEM column's formulas don't work. Can you manually create a small workbook with some actual test data?
Martin Liss

I put together the attached workbook.

I wasn't sure if you want four rows of data on the report sheet (one row each for "CUBIC" , "SHERE", "CLB" and "UNDER £1" ) or if you want to see all the data. The attached workbook does the latter, but it's easily changed. Every time to activate the "report" sheet it is refreshed.
29175580.xlsm
david matthews

ASKER
I think we are 99% there.

Is there anyway that instead of listing all individually on sheet called report can you make them into  running totals.

Lets say on sheet 1 there was  CUBIC      4      station A      11.00  of which is shown on a line in report of which is correct .

If there was then CUBIC      4      station A      110..00 for agruments sake in sheet 9  can I add that to the total thats already in the  report for that machine and also do it to all other machines too.

Also if I was to lock a sheet so I couldnt put any more data in would it still calculate etc.

Also I tried adding something different instead of cubic/ shere/uunder £1 and it fifnt recognise or update.  I need to be able to put any data in name column and it accept it as is for various people/machines  Dont worry about the CEM bit I will just delete that

I wasn't sure if you want four rows of data on the report sheet (one row each for "CUBIC" , "SHERE", "CLB" and "UNDER £1" )
I need 1 row with a running total for CLB" and "UNDER £1 and 1 row with running totals for each machine for CUBIC" , "SHERE",
Your help has saved me hundreds of hours of internet surfing.
fblack61
Martin Liss

Is there anyway that instead of listing all individually on sheet called report can you make them into  running totals.
Yes. Will all sheets that contain CUBIC (for example) always have the same values in columns C and D?

Also if I was to lock a sheet so I couldnt put any more data in would it still calculate etc.
Yes.

I need to be able to put any data in name column...
I can do that.
david matthews

ASKER
Is there anyway that instead of listing all individually on sheet called report can you make them into  running totals.

Yes. Will all sheets that contain CUBIC (for example) always have the same values in columns C and D?

NO they will vary depending on station and machine no.
Martin Liss

How many rows should be on the report sheet?

Four (for now)?

Or one row for each unique combination of Name+Machine number+Station+Amount?


Also please do me a favor and when you want to quote something that you or I said, please select the statement and then click the 'Quote' icon (4th from the right).
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
david matthews

ASKER
one row for each unique combination please as there will be about 40 different locations that I will be using.
Martin Liss

Try this workbook. As with the previous workbook the "report" sheet is refreshed every time it is activated. To make the this workbook run as fast as I can, I created a column on the "report" sheet that contains keys that are a concatenation of columns B, C and D from the  numbered sheets. I wanted to hide that column but for some reason that I don't understand right now, it needs to be visible so I made it column ZZ so it won't be seen unless you scroll way to the right.
29175580a.xlsm
Martin Liss

A simple change fixed that. Now column ZZ is hidden.
29175580b.xlsm
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
david matthews

ASKER
Many many Thanks
Martin Liss

You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2019
              Experts Exchange Top Expert VBA 2018, 2019
              Experts Exchange Distinguished Expert in Excel 2018
ASKER CERTIFIED SOLUTION
Martin Liss

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.