VBA for Copy & Transpose Paste

[Pratapherono1]

Hi VBA Expert,

Need your expertise in building a VBA to copy the data from Sheet 1 and transpose paste in Sheet 2 – As per the below format:

Sheet 1 (will have a pivot in the below format) - Data needs to be copied from this sheet:
[TABLE="width: 417"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]ID[/TD]
[TD]GBP[/TD]
[TD]USD[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD][/TD]
[TD]220675.50[/TD]
[TD]220675.50[/TD]
[/TR]
[TR]
[TD]11233[/TD]
[TD][/TD]
[TD]-6180.33[/TD]
[TD]-6180.33[/TD]
[/TR]
[TR]
[TD]27736[/TD]
[TD]-18997.79[/TD]
[TD]-2445.00[/TD]
[TD]-21442.79[/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]-18997.79[/TD]
[TD]212050.17[/TD]
[TD]193052.38[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2 (will have a table in the below format) – Data needs to be pasted in this sheet:

[TABLE="width: 701"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]S. No.[/TD]
[TD]Batch[/TD]
[TD]Date[/TD]
[TD]Date[/TD]
[TD]Type[/TD]
[TD]ID[/TD]
[TD]CCY[/TD]
[TD]Amount[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Batch1[/TD]
[TD]7-Sep-18[/TD]
[TD]7-Sep-18[/TD]
[TD]ABC[/TD]
[TD]12345[/TD]
[TD]USD[/TD]
[TD]220,675.50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Batch1[/TD]
[TD]7-Sep-18[/TD]
[TD]7-Sep-18[/TD]
[TD]XYZ[/TD]
[TD]11233[/TD]
[TD]USD[/TD]
[TD]-6,180.33[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Batch1[/TD]
[TD]7-Sep-18[/TD]
[TD]7-Sep-18[/TD]
[TD]XYZ[/TD]
[TD]27736[/TD]
[TD]GBP[/TD]
[TD]-18,997.79[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Batch1[/TD]
[TD]7-Sep-18[/TD]
[TD]7-Sep-18[/TD]
[TD]XYZ[/TD]
[TD]27736[/TD]
[TD]USD[/TD]
[TD]-2,445.00[/TD]
[/TR]
</tbody>[/TABLE]


VBA Parameters:
* Data from column A, B & C (in Sheet 1) to be copied and pasted accordingly in F, G & H (in Sheet 2) – Please note that the data in Sheet 2 is historical – Hence, the paste should be based on next available row (in Col F) in Sheet 2.
* Sheet 2 – Col A – Should have serial number in series
* Sheet 2 – Col B – Should have the number of times – the macro is run on a given day – If it is run 1^st time – then it should have Batch 1, if it is run 2^nd Time – then it should have Batch 2 etc…
* Sheet 2 - Col C & D – Should have today’s date


Note 1: Data in Sheet 1 is always changed (i.e, Pivot table is refreshed all the time). However, data in Sheet 2 is static data dump – the paste should always be added to the existing list (i.e. next available row).
Note 2: In the above example – there are just 3 IDs & 2 Currencies in the pivot – there could be upto 50 IDs & 30 Currencies (might differ based on data).
Note 3: There could be IDs (in Column A of Sheet 1) – with more than 1 currency (In the above example – ID # 27736 is available in GBP & USD). Hence, it has to be split accordingly in Sheet 2.


I spend almost 4 hours a day on this manual copy & paste – your help in building a VBA will be of great save.

Thank you my lord!
Regards,
Pratap

 

[Pratapherono1]

Hi,

Any help on this - would be a life saver.
Please assist.

Thank you!

 

[lrobbo314]

I have a couple of questions.

What cells does the pivot table occupy? e.g. A3:D7?
On sheet2, where is the type coming from? e.g. ABC, XYZ

Also, to be able to set up the pivot table range I would like to know how your pivot table is set up. I'm trying to replicate it here. Do you have 2 header rows? In my test data,
A3 says 'Sum of Amount', B3 says 'Type',
A4 says 'ID', B4 says 'GBP', and C4 says 'USD'.

Is that what your pivot table looks like?