Creating a new column through a column sub-header

[naz]

I tried to look around but it seems like I can't find the right answer
for my question.

I have a data group by a header & sub-header. See example below:


****************************
A B C D << column header
Location: California << Sub-header
23 25 21 20 << Data
10 11 41 78
45 32 21 67
Total 165 << Sub-total
Location: Nevada << Sub-header
27 45 26 10 << Data
18 13 31 48
43 82 41 17
Total 75 << Sub-total
****************************

What I wanted to do is to add the subheader as a column for each line
****************************
A B C D Location <<
column header
Location: California <<
Sub-header
23 25 21 20 California <<
Data
10 11 41 78 California
45 32 21 67 California
Total 165 California <<
Sub-total
Location: Nevada << Sub-
header
27 45 26 10 Nevada << Data
18 13 31 48 Nevada
43 82 41 17 Nevada
Total 75 Nevada <<
Sub-total
****************************

This way I can use my data for better manipulation with less manual
formatting.

PLEASE HELP.

 

[Guest]

Sub movelocation()

RowCount = 1
Do While Cells(RowCount, "A").Value <> ""
If Cells(RowCount, "A").Value = "Location:" Then
Location = Trim(Cells(RowCount, "B").Value)
Else
If Location <> "" Then
Cells(RowCount, "E").Value = Location
End If
End If

RowCount = RowCount + 1
Loop

End Sub