Excel VBA to COPY and Paste value based on match criteria in same sheet

_Google

New Member
Joined
Feb 6, 2013
Messages
40
Hi

Am new to VBA, and trying to create code based macro recording ( used autofilter ..option) but not able to get desidred output code

I have below table , Table is in Col D to Col G as below where "XXX" is the criteria for which entire range has to be selected and copied . Thereafter selected range is to be pasted below last row of the current range for number of unique-Code count. However there caveat.... that is

1. for 1st copy and paste... it has to select the range when criteria "XXX" of Col B2 is matched and then copy paste below last row of current data range than it has to replace "XXX" for 1st instance pasted value with code in Col A5 ( Value = A)

2 . Then for 2nd copy and paste...it has to select the range when criteria "XXX" of Col B2 is matched and then copy paste below last row of current data range than it has to replace "XXX" for 2nd instance pasted value with code in Col A5 ( Value = B)


Below is data table structure
[TABLE="width: 604"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4498" width=123><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1536" width=42><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 48pt" span=3 width=64><TBODY>[TR]
[TD="class: xl115, width: 64, bgcolor: transparent"]Col A[/TD]
[TD="class: xl115, width: 123, bgcolor: transparent"]Col B[/TD]
[TD="class: xl115, width: 42, bgcolor: transparent"][/TD]
[TD="class: xl116, width: 64, bgcolor: transparent"]Col d[/TD]
[TD="class: xl117, width: 64, bgcolor: transparent"]Col e[/TD]
[TD="class: xl117, width: 64, bgcolor: transparent"]Col f[/TD]
[TD="class: xl118, width: 64, bgcolor: transparent"]Col g[/TD]
[TD="class: xl115, width: 37, bgcolor: transparent"][/TD]
[TD="class: xl116, width: 90, bgcolor: transparent"]Col d[/TD]
[TD="class: xl117, width: 64, bgcolor: transparent"]Col e[/TD]
[TD="class: xl117, width: 64, bgcolor: transparent"]Col f[/TD]
[TD="class: xl118, width: 64, bgcolor: transparent"]Col g[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"]Criteria[/TD]
[TD="class: xl110, bgcolor: yellow"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl109, bgcolor: yellow, colspan: 4"]Before[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl112, bgcolor: #ffc000, colspan: 4"]After[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl120, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"] [/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl120, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl121, bgcolor: #fde9d9"]code[/TD]
[TD="class: xl115, bgcolor: transparent"]UniqueCode-Count[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl122, bgcolor: #fde9d9"]Unit[/TD]
[TD="class: xl121, bgcolor: #fde9d9"]code[/TD]
[TD="bgcolor: transparent"]<v:shapetype id=_x0000_t75 coordsize="21600,21600" o:spt="75" o:preferrelative="t" path="m@4@5l@4@11@9@11@9@5xe" filled="f" stroked="f"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><v:path o:extrusionok="f" gradientshapeok="t" o:connecttype="rect"></v:path><o:lock v:ext="edit" aspectratio="t"></o:lock></v:shapetype>

<TBODY>
[TD="class: xl123, width: 64, bgcolor: #d4e2ee"] month [/TD]

</TBODY>
[/TD]
[TD="bgcolor: transparent"]

<TBODY>
[TD="class: xl124, width: 64, bgcolor: #d4e2ee"] Year [/TD]

</TBODY>
[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl122, bgcolor: #fde9d9"]Unit[/TD]
[TD="class: xl121, bgcolor: #fde9d9"]code[/TD]
[TD="bgcolor: transparent"]

<TBODY>
[TD="class: xl123, width: 64, bgcolor: #d4e2ee"] month [/TD]

</TBODY>
[/TD]
[TD="bgcolor: transparent"]

<TBODY>
[TD="class: xl124, width: 64, bgcolor: #d4e2ee"] Year [/TD]

</TBODY>
[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]3[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]11.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]3[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]11.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"]TOTAL[/TD]
[TD="class: xl110, bgcolor: yellow"]2[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]12.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]11.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]11.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]5[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]10.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl125, bgcolor: transparent"]9[/TD]
[TD="class: xl126, bgcolor: transparent"]XXX[/TD]
[TD="class: xl126, bgcolor: transparent"]5.2014[/TD]
[TD="class: xl127, bgcolor: transparent"]2014[/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl119, bgcolor: transparent"]9[/TD]
[TD="class: xl115, bgcolor: transparent"]XXX[/TD]
[TD="class: xl115, bgcolor: transparent"]5.2014[/TD]
[TD="class: xl120, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl128, bgcolor: transparent"]2[/TD]
[TD="class: xl129, bgcolor: transparent"]A[/TD]
[TD="class: xl129, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl130, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]9[/TD]
[TD="class: xl115, bgcolor: transparent"]A[/TD]
[TD="class: xl115, bgcolor: transparent"]5.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]4[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]2[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]1[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]6.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]8.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl131, bgcolor: transparent"]50[/TD]
[TD="class: xl115, bgcolor: transparent"]B[/TD]
[TD="class: xl115, bgcolor: transparent"]7.2014[/TD]
[TD="class: xl132, bgcolor: transparent"]2014[/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl115, bgcolor: transparent"][/TD]
[TD="class: xl133, bgcolor: transparent"]9[/TD]
[TD="class: xl134, bgcolor: transparent"]B[/TD]
[TD="class: xl134, bgcolor: transparent"]5.2014[/TD]
[TD="class: xl135, bgcolor: transparent"]2014[/TD]
[/TR]
</TBODY>[/TABLE]
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Code:
[color=darkblue]Sub[/color] Criteria_Match_Copy()
    [color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range, LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]If[/color] Application.CountIf(Range("E5:E" & LastRow), Range("B2")) [color=darkblue]Then[/color]
        Range("E4:E" & LastRow).AutoFilter 1, Range("B2").Value
        [color=darkblue]Set[/color] rng = Range("D5:D" & LastRow).SpecialCells(xlCellTypeVisible)
        ActiveSheet.AutoFilterMode = [color=darkblue]False[/color]
        rng.Resize(, 4).Copy Destination:=Range("D" & LastRow + 1)
        Range("E" & LastRow + 1).Resize(rng.Count).Value = Range("A5").Value
        rng.Resize(, 4).Copy Destination:=Range("D" & LastRow + rng.Count + 1)
        Range("E" & LastRow + rng.Count + 1).Resize(rng.Count).Value = Range("A6").Value
    [color=darkblue]Else[/color]
        MsgBox "No match for '" & Range("B2").Value & "'. ", vbExclamation, "No Criteria Match"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Hi Alpha,

Its Apha code which is working like charm and magic , god bless for this ..am also studing it with my recorder base code. May take your persmission to add complexcity

table structure and problem remain the same.... the complexcity is on column - CODE and UniqueCode-Count , I want to paste of "XXX" range number of time equal the Total againt of UniqueCode-Count and also repalce "XXX" with A..B..C ( if it help to loop I can identity for A as 1, B as 2 in UniqueCode-Count and get Total as 5 by countif)

Looking forward .....and thanks in advance


[TABLE="width: 485"]
<TBODY>[TR]
[TD]Col A</SPAN>
[/TD]
[TD]Col B</SPAN>
[/TD]
[TD][/TD]
[TD]Col d</SPAN>
[/TD]
[TD]Col e</SPAN>
[/TD]
[TD]Col f</SPAN>
[/TD]
[TD]Col g</SPAN>
[/TD]
[/TR]
[TR]
[TD]Criteria</SPAN>
[/TD]
[TD]XXX</SPAN>
[/TD]
[TD][/TD]
[TD="colspan: 4"]Before</SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]code</SPAN>
[/TD]
[TD="colspan: 2"]UniqueCode-Count</SPAN>
[/TD]
[TD]Unit</SPAN>
[/TD]
[TD]code</SPAN>
[/TD]
[TD]month</SPAN>
[/TD]
[TD]Year </SPAN>
[/TD]
[/TR]
[TR]
[TD]A</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]5</SPAN>
[/TD]
[TD]A</SPAN>
[/TD]
[TD="align: right"]12.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]B</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]3</SPAN>
[/TD]
[TD]B</SPAN>
[/TD]
[TD="align: right"]11.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]C</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]2</SPAN>
[/TD]
[TD]C</SPAN>
[/TD]
[TD="align: right"]10.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]D</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD]D</SPAN>
[/TD]
[TD="align: right"]10.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]E</SPAN>
[/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]1</SPAN>
[/TD]
[TD]E</SPAN>
[/TD]
[TD="align: right"]10.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]F</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50</SPAN>
[/TD]
[TD]XXX</SPAN>
[/TD]
[TD="align: right"]8.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]G</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50</SPAN>
[/TD]
[TD]XXX</SPAN>
[/TD]
[TD="align: right"]7.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]H</SPAN>
[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50</SPAN>
[/TD]
[TD]XXX</SPAN>
[/TD]
[TD="align: right"]7.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
[TR]
[TD]TOTAL</SPAN>
[/TD]
[TD]5</SPAN>
[/TD]
[TD][/TD]
[TD="align: right"]9</SPAN>
[/TD]
[TD]XXX</SPAN>
[/TD]
[TD="align: right"]5.2014</SPAN>
[/TD]
[TD]2014</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
Code:
[color=darkblue]Sub[/color] Criteria_Match_Copy()
    [color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range, LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color], cell [color=darkblue]As[/color] Range
    LastRow = Range("E" & Rows.Count).End(xlUp).Row
    [color=darkblue]If[/color] Application.CountIf(Range("E5:E" & LastRow), Range("B2")) [color=darkblue]Then[/color]
        Application.ScreenUpdating = [color=darkblue]False[/color]
        Range("E4:E" & LastRow).AutoFilter 1, Range("B2").Value
        [color=darkblue]Set[/color] rng = Range("D5:D" & LastRow).SpecialCells(xlCellTypeVisible)
        ActiveSheet.AutoFilterMode = [color=darkblue]False[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("A5", Range("A" & Rows.Count).End(xlUp).Offset(-1))
            [color=darkblue]If[/color] cell.Offset(, 1).Value <> "" [color=darkblue]Then[/color]
                [color=darkblue]With[/color] Range("D" & Rows.Count).End(xlUp).Offset(1)
                    rng.Resize(, 4).Copy Destination:=.Cells
                   .Offset(, 1).Resize(rng.Count).Value = cell.Value
                [color=darkblue]End[/color] [color=darkblue]With[/color]
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]Next[/color] cell
        Application.ScreenUpdating = [color=darkblue]True[/color]
    [color=darkblue]Else[/color]
        MsgBox "No match for '" & Range("B2").Value & "'. ", vbExclamation, "No Criteria Match"
    [color=darkblue]End[/color] [color=darkblue]If[/color]
End [color=darkblue]Sub[/color]
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,119
Members
452,381
Latest member
Nova88

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