Hello Excel experts
I would like to ask you for favor.
I am getting a report every month with data covering last three months (see example in the attachment). On the "raw" sheet are the data what I got for processing. I would like to have a macro which will count average of data in column "D" for every month separately. Then the result will be loaded to a table like you can see on the "avg" sheet. The macro should be executable by hitting the "count button".
All replies are appreciated.
Thank you in advance.
Have you thought about using a pivot table?
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi,Originally Posted by Domski
Yes I know pivot table is an option, but for this I would need a solution as I described. The attached report is just a small part of a much bigger entity.
Thank you for you advise.
You wouldn't want to use a pivot to summarise the data then? It would be quite easy to put together a macro to create one automatically.
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
The length of raw data differs every month as each month has different count of day so I am bit confused by defining input data for the pivot table. I don't want to do that manually. Would it be possible to automatize it? Could you please show me an example?
Thank you
How about this?
Sub Test() Dim PTCache As PivotCache Dim PT As PivotTable Rows("1:1").Insert Range("A1") = "Date" Range("B1") = "Time" Range("C1") = "Data 1" Range("D1") = "Data 2" Range("E1") = "Data 3" Range("F1") = "Data 4" Range("G1") = "Data 5" Range("H1") = "Data 6" Set PTCache = ActiveWorkbook.PivotCaches.Add(xlDatabase, Range("A1").CurrentRegion.Address) Set PT = PTCache.CreatePivotTable(TableDestination:="", TableName:="PivotTable1") PT.PivotFields("Date").Orientation = xlRowField PT.PivotFields("Data 2").Orientation = xlDataField PT.PivotFields("Sum of Data 2").Function = xlAverage PT.PivotFields("Date").DataRange.Cells(1).Group Start:=True, End:=True, _ Periods:=Array(False, False, False, False, True, False, True) End Sub
Dom
"May the fleas of a thousand camels infest the crotch of the person who screws up your day and may their arms be too short to scratch..."
Use code tags when posting your VBA code: [code] Your code here [/code]
Remember, saying thanks only takes a second or two. Click the little star to give some Rep if you think an answer deserves it.
Hi Dom,
Thank you very much for this code. It works perfectly in a stand-alone solution but it is not implementable into a complex reporting system. I would really appreciate if you can help me to figure out my original request.
Thank you
Last edited by shg; 06-17-2011 at 10:40 AM. Reason: deleted quote
Link, please don't quote whole posts; it just clutters the thread. Use the REPLY button, not the QUOTE button.
Thanks.
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
Can anybody help me to figure it out please?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks