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
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
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?
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
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
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",
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",
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.
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.
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.
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).
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).
ASKER
one row for each unique combination please as there will be about 40 different locations that I will be using.
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
29175580a.xlsm
A simple change fixed that. Now column ZZ is hidden.
29175580b.xlsm
29175580b.xlsm
ASKER
Many many Thanks
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.