Compiling data from multiple work sheets.

Hi I'm having trouble making a printable/exportable summary page for an excel sheet I'm working on.

This sheet has multiple worksheets each with a similar format but there not identical dealing with different products. I want to have a worksheet that posts all the rows with an amount more than 1. And under set headings identical to the ones in the worksheet. I can link to the sheet if needed as its nothing important data wise.

I was wondering how I could do this I may need to use macros or VB but I am unsure.

Thanks for any help.

Hi, welcome to the forum.

It's probably easier for us to offer a solution if you attach your workbook in the forum.

If it's sensitive data then anonymise it, and if it's very large delete all but a sample of the data. Add some notes describing the cells you want to deal with and the results you expect and I'm sure someone will be along to help.

Rgds

OK then here is a link to a version of the sheet.
http://dc81.2shared.com/download/347...75808-5473a182

All though it looks big is only about 500Kb.

When you say you want all rows with an amount more than 1, how are you defining 'amount'. Are these all the columns headed "Total"?

Then what data do you want included on your summary sheet? Since the column headings are significantly different, how are you expecting them to line up?

Can you provide an example of the summary layout you're looking for?

Rgds

Hi Dumori,

I have written a Macro 'compile_final'. This compiles the data from the first five worksheets and inserts the results into a sheet named 'Final'.

Since, the code for each sheet remains the same, you can implement it for the rest of the sheets yourself.
Since, the value of 'Total' in each of the first five sheets is 0, I manually changed the values during debugging. This has been done to display the layout of the complied data which is in the sheet "Final".

Please have a look at the attachment.

Regards,
Amreen

Hi Chrysalis,

Thanks for attaching the Summary sheet. It gave a better understanding of how the final output should look like.

I have created the Macro "compile_final". Executing it would compile all the rows from Sheets "Melee & Projectile Weapons" and "Firearms" which have the value of amount greater than 1. These rows would be inserted into a separate sheet named "Summary".

The attached workbook shows the "Summary" sheet. Let me know if this is what you need.

Regards,
Amreen

Yep this is what we want. On my pc when I try to compile the summary just for the first sheets same macro as yours I keep getting a box saying 400 the cancel or help.

Hi

The excel workbook in the attachment shows the Summary sheet and this has been generated using the Macro.

Please note the following things:
1) I had altered data in the original workbook so that there were some non- zero values in Column C 'Amount' in the existing sheets.

2) Make sure that the Macros are being executed for the correct workbook. In case the name of the workbook has changed, kindly copy the macro in the workbook in which you want a Summary sheet.

Can you please tell me what exactly the error 400 says.
There can be a discrepancy in the names of the sheets or the workbook. And since these names have been hardcoded in the macros, they can be changed in the Macro to generate the correct result.

Regards

I would like to have help in trying to make a final page that adds up all totals in other tabs in the same workbook,

I have uploaded my document. I would appreciate any help you can offer.

thanks again

Rick

Bogus link, does'nt opens