create macro to create pivot table and change format

i have attached 2 worksheets.
testA and testB
first is it possible with a pivot table to change data in testA to look like testB?
second- can a macro be created to do it?
thank you
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Patrick MatthewsConnect With a Mentor Commented:
Yes, you can.  It is basically a PT with name and ID as row fields, date as column field, and sum of hours as data field.

The following macro seems to be working for me:

Sub MakePT()

    ActiveSheet.ListObjects.Add(xlSrcRange, Range("a1").CurrentRegion, , xlYes).Name = _
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="MyTable") _
        .CreatePivotTable TableDestination:="", TableName:=""
    With ActiveSheet.PivotTables(1)
        With .PivotFields("name")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("ID#")
            .Orientation = xlRowField
            .Position = 2
        End With
        With .PivotFields("date")
            .Orientation = xlColumnField
            .Position = 1
        End With
        .AddDataField .PivotFields("TotalHours"), "Sum of TotalHours", xlSum
        .RowAxisLayout xlTabularRow
        .PivotFields("name").Subtotals = Array(False, False, False, False, False, False, _
            False, False, False, False, False, False)
    End With
End Sub

Open in new window

I made the source range into a Table; for more on that please see:
davetoughAuthor Commented:
thanks works great
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.