Transposing data into new format

[EmmieLou]

Hi,
Am trying to create a macro in Excel that will take different datasets and
transpose data into a different format. I have species names and counts in
sets across columns. A and B are a set (Location A), C and D (Location B) a
set, etc. Would like to have all the information in A and B only, with row C
location data. How do I take the data out of C and D or E and F and paste it
below A and B? The data varies across several sheets and while the columns A
and B have the same number of entries in one wheet, they maybe different in
another sheet.
Example (Have)
A B C D E F
1 a 1 f 6 h 8
2 b 2 g 7
3 c 3
4 d 4
5 e 5
Want
A B C
1 a 1 Location A
2 b 2 Location A
3 c 3 Location A
4 d 4 Location A
5 e 5 Location A
6 f 6 Location B
7 g 7 Location B
8 h 8 Location C

 

[Dick Kusleika]

A B C D E F
1 a 1 f 6 h 8
2 b 2 g 7
3 c 3
4 d 4
5 e 5

Sub TransData()

Dim i As Long, j As Long
Dim vaData As Variant
Dim rOutput As Range
Dim lCount As Long

vaData = Sheet1.UsedRange.Value2
Set rOutput = Sheet1.Cells(Sheet1.UsedRange.Rows.Count + 10, 1)

For i = LBound(vaData, 2) To UBound(vaData, 2) Step 2
For j = LBound(vaData, 1) To UBound(vaData, 1)
If Len(vaData(j, i)) > 0 Then
rOutput.Offset(lCount, 0).Value = vaData(j, i)
rOutput.Offset(lCount, 1).Value = vaData(j, i + 1)
rOutput.Offset(lCount, 2).Value = "Location " & Chr$((i \ 2)
+ 65)
lCount = lCount + 1
End If
Next j
Next i

End Sub

Watch for line wrap.