Copy data from one sheet to another

[Pratapherono1]

Hi there,

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]


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.

Note: 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).

Your help in building a VBA will be of great save.

 

[My Aswer Is This]

In your image:
The data I see in column A B and C of sheet 1
Is not the same data I see in column F G and H of sheet 2

You said:

Data from column A, B & C (in Sheet 1) to be copied and pasted accordingly in F, G & H (in Sheet 2)​

 

[Pratapherono1]

Hi there,

You are correct. It is not a direct copy & paste from Sheet 1 to Sheet 2.
The data from sheet 1 has to be copied and pasted into the corresponding column in Sheet 2.

For Example:
Col A (in Sheet 1) will have the ID Number - This has to be copied and pasted into Col F (in Sheet 2)

Similarly, every ID (in Sheet 1) will have different currency listed in Col B, C, D and so on.
For every currency, a new line should be created in Sheet 2, the currency name has to be listed in Col G and its corresponding amount in Col H.

I hope, I did not confuse you.
Please let me know.

Thanks

 

[Pratapherono1]

Hi,

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

Thank you!