Macro to buid Pivot Table with Dynamic range

Hi All,

The attached file contains my raw data and desired macro.

I want to automate this function as this is a small piece of a larger project.

I tried recording the macro as I dont have much experience in coding with pivot tables. But the recorded macro fails on the second run and gives different error each time. Please can someone help and guide me to a build a code manually.

One more issue is that the raw data range can change (especially the number of columns). But the data that needs to be picked for HTR and their headers are always same as shown in sheet. Hency the range pickup should be dynamic.

Request your help!

Thanks,
Andy