Paste Special values from individual columns - vba

[pandora1234]

Hi I have to copy values from columns A, D and E from one worksheet to the same columns in another worksheet. I want to use vba as its a fiddly process otherwise. It would be easier to copy the whole range of data initially from A to F, but then click a button on the second worksheet to paste special the selected column values into the second worksheet. Any ideas how I could do this? I'm using excel 2007, and would assign the macro to the quick toolbar at the top. Thanks

 

[venkat1926]

data in sheet1 like this

Sheet1

*	A	B	C	D	E	F

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]61[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]81[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]16[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: right"]94[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]97[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]37[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]76[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]81[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]96[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]35[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]65[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]89[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4




try this macro and SEE SHEET2

Sub test()
Dim col(1 To 3), j As Long
Worksheets("sheet2").Cells.Clear
col(1) = "A"
col(2) = "D"
col(3) = "E"




With Worksheets("sheet1")
For j = 1 To 3
Range(.Cells(1, col(j)), .Cells(1, col(j)).End(xlDown)).Copy
With Worksheets("sheet2")
.Cells(1, j).PasteSpecial
End With
Next j
End With
End Sub