Pratapherono1
New Member
- Joined
- Sep 10, 2018
- Messages
- 8
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:
<tbody>
</tbody>
Sheet 2 (will have a table in the below format) – Data needs to be pasted in this sheet:
<tbody>
</tbody>
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 1st time – then it should have Batch 1, if it is run 2nd 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
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:
A | B | C | D |
ID | GBP | USD | Grand Total |
12345 | 220675.50 | 220675.50 | |
11233 | -6180.33 | -6180.33 | |
27736 | -18997.79 | -2445.00 | -21442.79 |
Grand Total | -18997.79 | 212050.17 | 193052.38 |
<tbody>
</tbody>
Sheet 2 (will have a table in the below format) – Data needs to be pasted in this sheet:
A | B | C | D | E | F | G | H |
S. No. | Batch | Date | Date | Type | ID | CCY | Amount |
1 | Batch1 | 7-Sep-18 | 7-Sep-18 | ABC | 12345 | USD | 220,675.50 |
2 | Batch1 | 7-Sep-18 | 7-Sep-18 | XYZ | 11233 | USD | -6,180.33 |
3 | Batch1 | 7-Sep-18 | 7-Sep-18 | XYZ | 27736 | GBP | -18,997.79 |
4 | Batch1 | 7-Sep-18 | 7-Sep-18 | XYZ | 27736 | USD | -2,445.00 |
<tbody>
</tbody>
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 1st time – then it should have Batch 1, if it is run 2nd 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