Hi All,
My data in below format.
1) Sheet Name PCI : A4:D column contains data , which I am getting by running below code
2) Now suppose my output in range A4: D6 came after running the below macro is :-
[TABLE="width: 194"]
<tbody>[TR]
[TD]ID_Cell[/TD]
[TD]PCI[/TD]
[TD]RRS[/TD]
[TD]Cell Range(km)[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col></colgroup>[/TABLE]
3) Column H1 to L3 contains the template lines which I need to repeat & concatenate based on above output.
Basically ID_Cell will be repeated 3 times because template having 3 rows and blank cell will be fill respectively with column PCI/RRS/Range(KM). Below is the sample output which needs to start from range H5 to L till last row.
Could you please assist , how to update the below code to get the below output.
Sample Template :-
[TABLE="width: 335"]
<tbody>[TR]
[TD]AA[/TD]
[TD]CellName=[/TD]
[TD]case1[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=[/TD]
[TD]case2[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=[/TD]
[TD]case3[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
Sample Output :-
[TABLE="width: 366"]
<tbody>[TR]
[TD]AA[/TD]
[TD]CellName=A123[/TD]
[TD]case1[/TD]
[TD="align: right"]1[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=A123[/TD]
[TD]case2[/TD]
[TD="align: right"]2[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=A123[/TD]
[TD]case3[/TD]
[TD="align: right"]3[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]CellName=B123[/TD]
[TD]case1[/TD]
[TD="align: right"]4[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=B123[/TD]
[TD]case2[/TD]
[TD="align: right"]5[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=B123[/TD]
[TD]case3[/TD]
[TD="align: right"]6[/TD]
[TD]Data[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
Regards,
Rajender
My data in below format.
1) Sheet Name PCI : A4:D column contains data , which I am getting by running below code
2) Now suppose my output in range A4: D6 came after running the below macro is :-
[TABLE="width: 194"]
<tbody>[TR]
[TD]ID_Cell[/TD]
[TD]PCI[/TD]
[TD]RRS[/TD]
[TD]Cell Range(km)[/TD]
[/TR]
[TR]
[TD]A123[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B123[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody><colgroup><col><col span="2"><col></colgroup>[/TABLE]
3) Column H1 to L3 contains the template lines which I need to repeat & concatenate based on above output.
Basically ID_Cell will be repeated 3 times because template having 3 rows and blank cell will be fill respectively with column PCI/RRS/Range(KM). Below is the sample output which needs to start from range H5 to L till last row.
Could you please assist , how to update the below code to get the below output.
Sample Template :-
[TABLE="width: 335"]
<tbody>[TR]
[TD]AA[/TD]
[TD]CellName=[/TD]
[TD]case1[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=[/TD]
[TD]case2[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=[/TD]
[TD]case3[/TD]
[TD]Blank Cell[/TD]
[TD]Data[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
Sample Output :-
[TABLE="width: 366"]
<tbody>[TR]
[TD]AA[/TD]
[TD]CellName=A123[/TD]
[TD]case1[/TD]
[TD="align: right"]1[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=A123[/TD]
[TD]case2[/TD]
[TD="align: right"]2[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=A123[/TD]
[TD]case3[/TD]
[TD="align: right"]3[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]AA[/TD]
[TD]CellName=B123[/TD]
[TD]case1[/TD]
[TD="align: right"]4[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]BB[/TD]
[TD]CellName=B123[/TD]
[TD]case2[/TD]
[TD="align: right"]5[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]CC[/TD]
[TD]CellName=B123[/TD]
[TD]case3[/TD]
[TD="align: right"]6[/TD]
[TD]Data[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
Code:
Sub Filter_PCI_1()
Sheets("PCI").Activate
Last_Row = Range("A4").End(xlDown).Offset(0).Row
Range("A5:L" & Last_Row).Select
Selection.Clear
Range("A4").Select
Application.CutCopyMode = False
Sheets("Plan PCI").Range("A1:R1048576").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:F2"), CopyToRange:=Range("A4:D4"), Unique:=False
End Sub
Regards,
Rajender