| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-30-07, 12:27
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
|
Macro that will SUM all the groups
|
|
Question
I have a very large spreadsheet that includes Month End Ship info to various customers.
Is there are a way to have a macro that will SUM all the groups I have?
Usually I go to the Data, Subtotal:
In the Add subtotal window - I check all Months Info
In the Use Function - I choose - Sum
And at each change in - i choose every group I have starting from Director - ending with StyleNum.
It is a long proceess, hope there are a way to have macro, which I never recorded.
Please help. Thank you.
Columns are:
Director
RegionDescription
Acct_Exec
AcctExec
Sold_To
Name
SalesGroup
ProductGroup
Fashion/Basic
FabricGroup
Style_Number
Color_Code
Total Units
Total Dollars
Jan'07 U
Jan'07 $
.
.
.
Dec'07 U
Dec'07 $
|
|
10-31-07, 14:53
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
Howdy. Can you post a sample? Anything you can do, can be done in some way with VBA.
|
|
10-31-07, 15:16
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
|
|
Please see file attached,
Thank you for your help
|
|
10-31-07, 15:18
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
Here the attachmnt -
Thank you
|
|
10-31-07, 15:41
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
Here is the intial code I had with my own data, before looking at your example.
Code:
Sub Macro1()
Dim MyData As Range, LastCell As Range
Dim lngRow As Long
Dim lngCol As Long
lngRow = Cells(Rows.Count, 1).End(xlUp).Row
lngCol = Cells(1, 256).Columns.End(xlToLeft).Column
Set MyData = Range("A1").Resize(lngRow, lngCol)
With MyData
.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(6, 7, 8, 9, _
10, 13), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End Sub
I will take a look at your sample.
|
|
10-31-07, 15:46
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
Thank you for the example - I will try to use it in excel macro.
What the Array(6, 7, 8, 9, 10, 13), Represent?
|
|
10-31-07, 15:53
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
Here is the code for your worksheet. I made a copy called "Test". You can run the code when that is the active worksheet (just to make sure it gives what you want)
Code:
Sub SumData()
Dim MyData As Range
Dim lngRow As Long
Dim lngCol As Long
lngRow = Cells(Rows.Count, 1).End(xlUp).Row
lngCol = Cells(1, 256).Columns.End(xlToLeft).Column
Set MyData = Range("A1").Resize(lngRow, lngCol)
With MyData
.Subtotal GroupBy:=1, Function:=xlSum, _
TotalList:=Array(17, 18, 19, 20, 21, 22, 23, 24, 25, 26, _
27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
End Sub
The Array numbers refer to the column numbers.
|
|
10-31-07, 15:59
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
If that works, great. But if you are doing this regularly, perhaps summarized data on separate worksheet makes more sense. That can be done with formulas or VBA (using Pivot Table which is more flexible than straight Pivot Tables).
|
|
10-31-07, 16:05
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
Thank you so much..., it is working perfectly for the Column Director. It subtotals all columns for this Director.
Is there are a way to have it for the below columns ????
I do really Appreciate your help .
Director
RegionDescription
AcctExec
Name
SalesGroup
ProductGroup
Fashion/Basic
FabricGroup
Style_Number
|
|
10-31-07, 16:07
|
|
Registered User
|
|
Join Date: Apr 2007
Location: Big City
Posts: 62
|
|
I'm learnign while working, Never use Pivot table. I need to learn more VBA loginc. new to it.
|
|
10-31-07, 16:29
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
Okay, perhaps a re-thinking of the data. Is the data layout set already (i.e. columns for each month, etc.)?
My thought here is that it would be better to have a column identified as Month, and then one column for Number, and one column for Amount. Then all data weill be vertical. I will provide an example.
What exactly are you trying to accomplish? It seems that Pivot Tables are much more practical. That can be done either through Excel or VBA.
Also, this is minimal data, can you provide maybe something 100 rows (with the variations)? In other words, I can't tell if I have helped with no variations int he columns you have.
|
Last edited by shades; 10-31-07 at 16:33.
|
10-31-07, 16:48
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
I added two worksheets: NewData has the arrangement I noted above. I added a dynamic named range, DataB, and it is defined as follows:
=OFFSET(NewData!$A$1,0,0,COUNTA(NewData!$A:$A),COU NTA(NewData!$1:$1))
That way you can add any new data and it will automatically adjust.
Then MyPivot is based on this NewData worksheet.
This might give you some ideas.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Macro that will SUM all the groups
