With your data table on the activesheet (with the first cell being A1), run this macro, then base the pivot tables and pivot charts on the new database.
Sub MakeDataBaseFromCrossTabTable2()
Dim shtDataBase As Worksheet
Dim shtData As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim m As Long
Dim rngData As Range
Dim iCols As Integer
Set shtData = ActiveSheet
Set rngData = shtData.Range("A1").CurrentRegion
iCols = 1
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("New Database").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set shtDataBase = Worksheets.Add
shtDataBase.Name = "New Database"
For m = 1 To iCols
shtDataBase.Cells(1, m).Value = _
rngData.Cells(1, m)
Next m
shtDataBase.Cells(1, m).Value = "Category"
shtDataBase.Cells(1, m + 1).Value = "Value"
i = 2
For j = rngData(1).Row + 1 To _
rngData(rngData.Cells.Count).Row
For k = rngData(1).Column + iCols To _
rngData(rngData.Cells.Count).Column
If shtData.Cells(j, k).Value <> "" Then
For m = 1 To iCols
shtDataBase.Cells(i, m).Value = _
shtData.Cells(j, rngData(m).Column).Value
Next m
shtDataBase.Cells(i, iCols + 1).Value = _
shtData.Cells(rngData(1).Row, k).Value
shtDataBase.Cells(i, iCols + 2).Value = _
shtData.Cells(j, k).Value
i = i + 1
End If
Next k
Next j
End Sub
LinkBack URL
About LinkBacks
Brazilian Joe 
Register To Reply
Bookmarks