Sub test()

Dim datash As Worksheet
Dim datarng As Range
Dim tsh As Worksheet
Dim startrng As Range
Dim startrng2 As Range
Dim endrng As Range
Dim endrng2 As Range
Dim copyrng As Range
Dim r2 As Range
Dim locatefirstcategory As Range
Dim L As Long
Dim startrng3 As Range
Dim cell As Variant




Set datash = ActiveSheet

' copying demographics range

Set datarng = datash.Cells(6, 2)
Set startrng = datarng


Do Until datarng = ""

    Set datarng = datarng.Offset(1, 0)

Loop

Set endrng = datarng(0, 1)

    datash.Range(datash.Cells(startrng.Row, datarng.Column), datash.Cells(endrng.Row, datarng.Column)).Select

    Sheets("Data").Activate

    datash.Range(datash.Cells(startrng.Row, datarng.Column), datash.Cells(endrng.Row, datarng.Column)).Copy Destination:=Sheets("Data").Range("C2")


' copying the measure values

Sheets("DEMO FOOD+OIL").Activate

Dim rng2 As Range


    Set rng2 = datash.Cells(5, 3)
    Set startrng2 = rng2


Dim measurestr As String
Dim periodstr As String


    measurestr = rng2(0, 1).Value
    periodstr = rng2.Value

Dim rng3 As Range
Set rng3 = Sheets("Data").Cells(2, 4)



Do Until rng2 = ""

    ' checking is there's a measure above the period.
    ' if not, look for the measure above the 1st period

Sheets("DEMO FOOD+OIL").Activate


    ' moving 1 column to the right, selecting it and copying the measure data

        datash.Range(datash.Cells(startrng.Row, rng2.Column), datash.Cells(endrng.Row, rng2.Column)).Select

        Sheets("Data").Activate

        datash.Range(datash.Cells(startrng.Row, rng2.Column), datash.Cells(endrng.Row, rng2.Column)).Copy Destination:=Sheets("Data").Range("D2")

    Set rng2 = rng2.Offset(0, 1)

    Sheets("DEMO FOOD+OIL").Activate

Loop

Stop


'copying the period

Dim datarng2 As Range


Sheets("Data").Activate

Set datarng2 = ThisWorkbook.Worksheets("Data").Cells(2, 2)

Do Until datarng2.Offset(0, 1) = ""

    datarng2.Value = periodstr

Set datarng2 = datarng2.Offset(1, 0)

Loop

Stop


Sheets("DEMO FOOD+OIL").Activate

'selecting the category in order to paste it to the Data sheet
'Dim rng3 As Range
Dim categorystr As String


    Set rng3 = datash.Cells(4, 2)
    'Set startrng3 = rng3

    categorystr = rng3.Value



' pasting the black Category to the datasheet next to the period

Sheets("Data").Activate

Dim datarng3 As Range

Set datarng3 = ThisWorkbook.Worksheets("Data").Cells(2, 1)


Do Until datarng3.Offset(0, 1) = ""

    datarng3.Value = categorystr

Set datarng3 = datarng3.Offset(1, 0)

Loop

Stop

End Sub