Copy and Paste Value Subtotals, Is it possible???

[joe73]

I'm trying to trim down my report by showing only the totals. It's a huge report. One sheet contains customers with products booked and shipped adding upto 30,000 lines plus other sheets containing analysis. I only want to show the total for each customer in a worksheet. The format of subtotal is exactly the way I want it but unable to get rid of details. I've tried to "copy" and "paste value only" in different ways but it does not work. It also paste all the details. Is there any other way to do this???

Thanks

 

[Telsa]

Have you created a separate spreadsheet that links only the subtotals? Or maybe if you want, hide the detailed rows or columns so only subtotals show.

Mary

 

[joe73]

Thanks for the quick response. Creating a separate spreadsheet is almost not an option. It's a floating report. Meaning I send it out to various VIP's and they send it out to whom it may concern. I don't want to confuse them anymore than I have to. The "Hide" function was a good suggestion. However, some detail still reside in the workbook and the file size virtually didn't change. If you think of anything else, please let me know.

Thanks

 

[JVFriederick]

If you have an extra column hanging around, and your data is fairly compact, consider using a filter.

Say your new column contains a 1 for each subtotal, and happens to be in column A. Also assume your upper left corner of your data set is named FilterStart, and there are continuous headings in each columm. The following macro code will show only the subtotals.


Range("FilterStart&quot.Select
Selection.AutoFilter ' Turn filter on with criteria
Selection.AutoFilter Field:=1, Criteria1:="1"

To turn filter off, run AutoFilter again :

Selection.AutoFilter ' Turn off filter

Adding a little button to toggle the totals on or off may work for you. VIP's LIKE to press buttons.

 

[joe73]

Thanks JVFriederick. I'll take it in consideration. I'll have to learn more about VBA however. I currently hold a Junior Data Analyst position. Say, do you happen to know an excellent book I can purchase on Excel? A book that holds intermediate and advance combine in one? What about Excel 2000 Bible Gold Edition? Or Excel 2000 Special Edition by QUE? What would you recommend?

Thanks

 

[JVFriederick]

Normally I'm not a tech book reader, but check out a local Barnes&Nobel etc and have a look. I prefer to choose more on the style of writing, to what level of experience the book was intended, and if there are worthwhile examples.

In my opinion, you really do not need to know much about VBA to use it within Excel, just jump in. In the example you gave, you could have a working solution in about 10 minutes I think. The only thing the code is doing is automating what can be done "manually" by selecting a few Excel menu commands.

If you want some more help with your project, there are plenty of people at this site who can help.

 

[ront02769]

Hi. Tough to call without seeing your structure but this SOUNDS like pivot tables might work pretty easily.

if you have, for example,


salesman product number
joe tires 1
joe wipers 2
john tires 4

and do a pivot table with salesman in the row field, product in the column field and number in the body you'll something like this

salesman tires wipers

joe 1 2
john 4 0

ront

 

[dbpcar]

TRY THIS
INSERT A WORKSHEET(CALL IT SUBTOTALS)
ON THE FIRST WORKSHEET NAME YOUR COLUMNS(NOT NECESSARY, BUT EASIER)
APPLY ADVANCED FILTER TO YOUR REFERENCE COLUMN
FILTER FOR UNIQUE RECORDS, SEND TO A LOCATION ON YOUR SECOND WORKSHEET(SUBTOTAL)
ON THE SUBTOTAL WORKSHEET WRITE A SUMIF STATEMENT
EXAMPLE:1ST WORKSHEET
SALESMAN AMOUNT
1234 1.23
1234 4.65
1234 102.65
2345 92.78
2345 14.65

SUBTOTAL SHEET
SALESMAN
USE THE ADVANCED FILTER FOR THIS COLUMN

AMOUNT2
=SUMIF(SALESMAN, B2,AMOUNT)

THIS IS NOT STEP BY STEP, YOU MAY HAVE TO PLAY WITH IT A LIITLE. IT WILL TAKE A WHILE TO RUN IF YOU HAVE 30,000 LINES OF DATA. ONCE YOU ARE SURE IT WORKS RIGHT, WRITE A MACRO TO DO IT FOR YOU IF THIS IS A RECURRING EVENT