|
|
|
|
It works perfectly! Thank you so much!!
"Debra Dalgleish" wrote:
You're welcome. If you're using Excel 2000, you can change the macro
slightly. Instead of:
.PivotTables("PivotTable1").DataPivotField _
.PivotItems("Sum of Value").Position = 1
use:
.PivotTables("PivotTable1").PivotFields("Data") _
.PivotItems("Sum of Value").Position = 1
Because you're using text, instead of numbers, in the data field, it
will default to "Count of Value", instead of "Sum of Value". To work
around this problem, add the following line at the top of the code:
On Error Resume Next
Then, after the line:
.PivotTables("PivotTable1").PivotFields("Data") _
.PivotItems("Sum of Value").Position = 1
add:
.PivotTables("PivotTable1").PivotFields("Data") _
.PivotItems("Count of Value").Position = 1
The revised procedure is:
'===========================
Sub MakeDataBaseTable()
On Error Resume Next
Dim SummaryTableRange As Range
Dim PivotTableSheet As Worksheet
Set SummaryTableRange = ActiveCell.CurrentRegion
If SummaryTableRange.Count = 1 Or _
SummaryTableRange.Rows.Count < 3 Then
MsgBox "Select a cell in the summary table.", vbCritical
Exit Sub
End If
ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlConsolidation, _
SourceData:=Array(SummaryTableRange _
.Address(True, True, xlR1C1, True))) _
.CreatePivotTable TableDestination:="", _
TableName:="PivotTable1"
Set PivotTableSheet = ActiveSheet
With PivotTableSheet
.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
.PivotTables("PivotTable1").PivotFields("Data") _
.PivotItems("Sum of Value").Position = 1
.PivotTables("PivotTable1").PivotFields("Data") _
.PivotItems("Count of Value").Position = 1
.PivotTables("PivotTable1").PivotFields("Row") _
.Orientation = xlHidden
.PivotTables("PivotTable1").PivotFields("Column") _
.Orientation = xlHidden
End With
Range("B4").ShowDetail = True
Application.DisplayAlerts = False
PivotTableSheet.Delete
Application.DisplayAlerts = True
End Sub
'=====================================
The Chad wrote:
Thanks Debra!! This works great!
I see the link to John Walkenbach's site includes the code for a VBA macro
that automates the process. I tried to create the module using the code but
got a "Run-time error '438' Object doesn't support the property or method"
error message. Do you (or anyone else) have an idea about how I should fix
that? I would love to use the VBA macro but cant seem to get the macro to
run without that error.
"Debra Dalgleish" wrote:
To reorganize the data, you can use the "unpivot' technique described by
John Walkenbach:
http://j-walk.com/ss/excel/usertips/tip068.htm
Then, sort by the Value column, and delete rows that don't contain an X.
Delete the Value column, and add your headings to the other columns.
The Chad wrote:
Hello everyone, this has been driving me crazy so want to see if any of you
have suggestions:
I have a grid in an Excel worksheet formatted like this:
Company A Company B Company C
Service 1 X X
Service 2 X X
Service 3 X X
I need to convert this data to a list that maps the Services to the
Companies like this:
Services Companies
Service 1 Company A
Service 1 Company C
Service 2 Company A
Service 2 Company B
Service 3 Company B
Service 3 Company C
I cant seem to find a good way to do this.
Any ideas??? Thanks all!
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
|