Hey all

I have a set of data in excel that I need rearranged so I can easily import it into access. The excel table is in the current format

Data type ______Unit______Jan 06______Feb 06______Mar 06_____ (...etc)
Coal consumed__tonnes____xxxxx________xxxxx________xxxx______(...etc)
Coal energy_____GJ_______xxxxx________xxxxx________xxxx______(...etc)
Electricity______kwh______xxxxx________xxxxx________xxxx______(...etc)
Natural Gas_____GJ_______xxxxx________xxxxx________xxxx______(...etc)
Slab production__tonnes___xxxxx________xxxxx________xxxx______(...etc)
(...etc)

This is an example table that could be of any date range and any number of Data types. I need to write a macro that will take a table of any size in this format, and change it into a table of this format

Data type________Value____Unit_______Date
Coal consumed____xxxx_____tonnes____Jan 06
Coal consumed____xxxx_____tonnes____Feb 06
Coal consumed____xxxx_____tonnes____Mar 06
Coal energy_______xxxx_____GJ_______Jan 06
Coal energy_______xxxx_____GJ_______Feb 06
Coal energy_______xxxx_____GJ_______Mar 06
(...etc)

I have got the following macro from a previous source

Sub test()
Dim a, i As Long, ii As Integer, b(), n As Long
With Range("a1")
With .CurrentRegion
a = .Value
.ClearContents
End With
ReDim b(1 To (UBound(a, 1) - 1) * (UBound(a, 2) - 2), 1 To 4)
For i = 2 To UBound(a, 1)
For ii = 3 To UBound(a, 2)
n = n + 1
b(n, 1) = a(i, 1): b(n, 3) = a(i, 2): b(n, 4) = a(1, ii)
Next
Next
.Resize(n, 4).Value = b
End With
End Sub
The problem with this is that it doesn't carry over the data (xxxx), it just leaves a blank column. Any help is greatly appreciated!