We help IT Professionals succeed at work.

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

david matthews
on
Medium Priority
52 Views
Last Modified: 2020-03-17
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
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 matthewsexcel newbe

Author

Commented:
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",
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 matthewsexcel newbe

Author

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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).
david matthewsexcel newbe

Author

Commented:
one row for each unique combination please as there will be about 40 different locations that I will be using.
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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 LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
A simple change fixed that. Now column ZZ is hidden.
29175580b.xlsm
david matthewsexcel newbe

Author

Commented:
Many many Thanks
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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
Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Note that if you insert a new column on the "report" sheet that you will need to change all ZZ to AAA (which is the next column) in the code. If you simply add data to a currently blank column you won't need to do that.