Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Copying SubTotals to new Sheet 1

Status
Not open for further replies.
smurf01

smurf01

IS-IT--Management
Jul 6, 2002
470
GB
Hi, I have a macro that creates a subtotal what I would like to do is copy the subtotals to a new sheet where i would then add some formulas to work out percentages. could someone explain how to incorporate the copying of the data to a new sheet please

The macro code is shown below

Sub SubTotal()
'
' SubTotal Macro
' Macro recorded 28/10/2003 by Paul Oliver
'

'
Selection.SubTotal GroupBy:=2, Function:=xlSum, TotalList:=Array(14, 15, 16 _
, 17), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

Regards

Paul
 
Sort by date Sort by votes
Skip,
When I do a pivot table the data is presented as shown below

*APOLLO CH Sum of OTIF_Y 8
Sum of OTIF_N 0
Sum of ONTIME_Y 8
Sum of ONTIME_N 0

However I would like the data presented as such :


*APOLLO CH Total 8 0 8 0

This is why I was hoping to be able to copy the subtotal data


Regards

Paul
 
Upvote 0 Downvote
Well, I've been frustrated by that seeming limitation too!

What I have done in cases such as this (and it happened this morning, believe it or not), is copy the pivot table and paste it next to the original, modify it to show the sum/count/avg etc of the field that you want. Then hide the columns you don't want to see..

:)

Skip,
Skip@TheOfficeExperts.com
 
Upvote 0 Downvote
Skip,
What I have actually done is by editing the code that you wrote for me for copying sheets, I have copied all my sheets containing monthly data into one sheet and then linked this to an Access database. I have then just written a query that gives me the data the way I want it and also added in the fields to give me the percentages.

Regards

Paul
 
Upvote 0 Downvote
Skip,
Did you give me a star, what's that for ??

Regards

Paul
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
How to resolve define worksheets array
Replies
1
Views
652
combo
combo
rf92d
  • Locked
  • Question
Hi referring to thread707-107529
Replies
0
Views
482
rf92d
rf92d
rf92d
  • Locked
  • Question
hi referring to thread707-103495
Replies
9
Views
615
Andrzejek
Andrzejek
aldi07
  • Locked
  • Question
update a front-end databases with a new release using vba
Replies
2
Views
306
aldi07
aldi07
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng

Part and Inventory Search

Sponsor

Back
Top