Timespread data transpose

[eschwanz]

Hello,

I am looking for some assistance in writing a macro to transpose timespread data that is coming out of a program that I use. I need to be able to make a calculation on the data, then put it back into a pivot table to spread it again by period. I only need help getting the data from timespread to data table. See example below. The columns 1 and 2 need to be repeated for every period. The periods, will be changing every time i run the report.

Thank you for your help.

From this
Project	Resource	Field	01-Dec	01-Jan	01-Feb	01-Mar	01-Apr	01-May	01-Jun
Job A	Resource 1	Hours	20	20	20	20	20	20	20
Job A	Resource 2	Hours		30	30	30	30
Job B	Resource 3	Hours			20	20	20	20	20
Job C	Resource 4	Hours		50	50	50	50	50
Job C	Resource 5	Hours	10	10	10	10	10
Job B	Resource 6	Hours		30	30	30	30	30	30

<colgroup><col><col><col><col><col><col span="4"><col></colgroup><tbody>
</tbody>

Into this
Project	Resource	Period	Hours
Job A	Resource 1	01-Dec	20
Job A	Resource 1	01-Jan	20
Job A	Resource 1	01-Feb	20
Job A	Resource 1	01-Mar	20
Job A	Resource 1	01-Apr	20
Job A	Resource 1	01-May	20
Job A	Resource 1	01-Jun	20
Job A	Resource 2	01-Dec
Job A	Resource 2	01-Jan	30
Job A	Resource 2	01-Feb	30
Job A	Resource 2	01-Mar	30
Job A	Resource 2	01-Apr	30
Job A	Resource 2	01-May
Job A	Resource 2	01-Jun
Job B	Resource 3	01-Dec
Job B	Resource 3	01-Jan
Job B	Resource 3	01-Feb	20
Job B	Resource 3	01-Mar	20
Job B	Resource 3	01-Apr	20
Job B	Resource 3	01-May	20
Job B	Resource 3	01-Jun	20
Job C	Resource 4	01-Dec
Job C	Resource 4	01-Jan	50
Job C	Resource 4	01-Feb	50
Job C	Resource 4	01-Mar	50
Job C	Resource 4	01-Apr	50
Job C	Resource 4	01-May	50
Job C	Resource 4	01-Jun
Job C	Resource 5	01-Dec	10
Job C	Resource 5	01-Jan	10
Job C	Resource 5	01-Feb	10
Job C	Resource 5	01-Mar	10
Job C	Resource 5	01-Apr	10
Job C	Resource 5	01-May
Job C	Resource 5	01-Jun
Job B	Resource 6	01-Dec
Job B	Resource 6	01-Jan	30
Job B	Resource 6	01-Feb	30
Job B	Resource 6	01-Mar	30
Job B	Resource 6	01-Apr	30
Job B	Resource 6	01-May	30
Job B	Resource 6	01-Jun	30

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this for results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG07Dec23
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 4)
nray(1, 1) = "project": nray(1, 2) = "Resource"
nray(1, 3) = "Periord": nray(1, 4) = "Hour"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 4 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1): nray(c, 2) = Ray(n, 2)
        nray(c, 3) = Format(Ray(1, Ac), "dd-mmm"): nray(c, 4) = Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[eschwanz]

Thanks Mick. This worked perfectly. In the last lines, I am having this feed into a pivot table, what do i need to have the data on sheet2 formatted as a table or dumped into a table? So i dont have to change the data source of the pivot table to captured new rows in the original data set.

 

[MickG]

If you change the last few lines as below it should convert the results data in sheet2 to a "Table".

With Sheets("Sheet2").Range("A1").Resize(c, 4)
   .Value = nray
   .Columns.AutoFit
End With
Sheets("Sheet2").ListObjects.Add(xlSrcRange, Sheets("Sheet2").Range("A1").Resize(c, 4), , xlYes).Name = "MyTable"