VBA Copy & Paste Data Loop

J

Johnathannn

New Member
Joined
Oct 25, 2019
Messages
2
Hello,

I am looking for VB code to copy data from one sheet, and paste it onto another sheet in a different format.



Here is the current format:


[TABLE="width: 545"]
<colgroup><col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" span="5"> <col width="137" style="width: 103pt; mso-width-source: userset; mso-width-alt: 5010;"> <col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;"> <tbody>[TR]
[TD="width: 70, bgcolor: #5B9BD5"]ID
[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="width: 137, bgcolor: #5B9BD5"]Total Previous Years [/TD]
[TD="width: 79, bgcolor: #5B9BD5"]Grand Total[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2066.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4438.73[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6703.19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]10705.06[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5785.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]23913.08[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]29698.58[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1166.12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2985.56[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6071.15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8415.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4548.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]18638.23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]23186.27[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]779.83[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]661.34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1441.17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1441.17[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1226.74[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3155.46[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6352.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8693.07[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5027.26[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]19427.77[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]24455.03[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2543.51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3297.14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2543.51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5840.65[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]181.28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]702.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4463.38[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6080.35[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2715.44[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]11427.51[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]14142.95[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1170.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2952.28[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5011.27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8757.77[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4548.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]17891.36[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]22439.4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]734.12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]325.95[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3220.54[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]7080.43[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3156.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]11361.04[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]14517.44
[/TD]
[/TR]
</tbody>[/TABLE]


Here is the desired format (on a new sheet):

[TABLE="width: 167"]
<colgroup><col width="70" style="width: 53pt; mso-width-source: userset; mso-width-alt: 2560;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 70, bgcolor: #5B9BD5"]ID[/TD]
[TD="width: 88, bgcolor: #5B9BD5"]Years[/TD]
[TD="width: 64, bgcolor: #5B9BD5"]Amount
[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2066.1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4438.73[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6703.19[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]10705.06[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]001
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5785.5[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1166.12[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2985.56[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6071.15[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8415.4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]003
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4548.04[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]779.83[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]661.34[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]187
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1226.74[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3155.46[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6352.5[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8693.07[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]010
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5027.26[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2543.51[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]006
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3297.14[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]181.28[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]702.5[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4463.38[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]6080.35[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]011
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2715.44[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]1170.04[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]2952.28[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]5011.27[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]8757.77[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]007
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]4548.04[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]734.12[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2015[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]325.95[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2016[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3220.54[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2017[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]7080.43[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]032
[/TD]
[TD="bgcolor: #5B9BD5"]Amount 2018[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BDD7EE]#BDD7EE[/URL] , align: right"]3156.4[/TD]
[/TR]
</tbody>[/TABLE]

Thanks!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Sort by date Sort by votes
Try this.
I assume the data begins in cell A1. And that in the 2 columns on the right you have totals and should not be considered.
Data on Sheet1, result on Sheet2, change data in red for your information.

Code: 
Sub Copy_Paste_Loop()
  Dim a() As Variant, i As Long, b() As Variant, sh1 As Worksheet
  Dim lr As Long, lc As Long, j As Long, k As Long
  Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
  lr = sh1.Range("A" & Rows.Count).End(xlUp).Row
  lc = sh1.Cells(1, Columns.Count).End(xlToLeft).Column - 2
  a = sh1.Range("A1", sh1.Cells(lr, lc)).Value
  ReDim b(1 To (lr - 1) * (lc - 1), 1 To 3)
  k = 1
  For i = 2 To UBound(a)
    For j = 2 To lc
      b(k, 1) = a(i, 1)
      b(k, 2) = a(1, j)
      b(k, 3) = a(i, j)
      k = k + 1
    Next
  Next
  Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("A2").Resize(UBound(b), 3).Value = b()
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
645
smozgur
smozgur
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
372
harzer
H
C
  • Locked
  • Question Question
Earned Value Formula Issue
Replies
1
Views
392
Fluff
Fluff
C
  • Locked
  • Question Question
Earned Value Formula Issue
Replies
1
Views
411
Fluff
Fluff
H
  • Solved
Capitalize the last character in column “K” conditionally.
Replies
3
Views
233
Fluff
Fluff

Forum statistics

Threads
1,224,823
Messages
6,181,169
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top