利用想定ケース:既存の票をpivotのデータとして使いたいとき
利用方法:表を選択した状態でマクロ実行

before

愛媛 和歌山
2012 100 110
2013 150 160

after

2012 愛媛 100
2012 愛媛 110
2013 和歌山 150
2013 和歌山 160
Option Explicit
Sub table2list()
Dim c As Range
Dim cols() As String
Dim i As Integer
Dim labelYColN As Integer
Dim labelXRowN As Integer
Dim pointer As Integer
ReDim cols(Selection.Columns.Count)
i = 1
labelYColN = Selection(1).Column
labelXRowN = Selection(1).Row
pointer = Selection(Selection.Count).Row + 2
Dim labelY As String
For Each c In Selection
If labelXRowN = c.Row Then
cols(i) = c.Value
i = i + 1
ElseIf labelYColN = c.Column Then
labelY = c.Value
Else
ActiveSheet.Cells(pointer, labelYColN).Value = labelY
ActiveSheet.Cells(pointer, labelYColN + 1).Value = cols(c.Column)
ActiveSheet.Cells(pointer, labelYColN + 2).Value = c.Value
pointer = pointer + 1
End If
Next
End Sub