Select cell +2 in column to copy data in another cell

[afc171]

Hi guys,

I have this VBA to select cell B3,C3 and D3 which copies to a column N5, N6 and N7

I would like to know how to select any cell in column B plus including the 2 cells next to it like above and copy these to the columns N5, N6 and N7

[COLOR=#011993][FONT=Menlo]Sub Cells()[/FONT][/COLOR][FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Dim[/COLOR] CopyData [COLOR=#011993]As[/COLOR] Range[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Dim[/COLOR] cell [COLOR=#011993]As[/COLOR] Range[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Dim[COLOR=#000000] x [/COLOR]AsInteger[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo]x = 0[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]Set CopyData = Range("[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]B3, C3, D3[/FONT][/COLOR][COLOR=#011993][FONT=Menlo]")[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]For[/COLOR] [COLOR=#011993]Each[/COLOR] cell [COLOR=#011993]In[/COLOR] CopyData[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    Range("N5").Offset(x) = cell.Value[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]    x = x + 1[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo][COLOR=#011993]Next[/COLOR] cell[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]

anyone help me out please?

Thanks you

 

[Eric W]

If you don't want to transpose the data, just remove the Transpose function:

[COLOR=#000000][FONT=Menlo][COLOR=#011993]If[/COLOR] ActiveCell.Column = 2 [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Range("B4:E4") = ActiveCell.Resize(, 4)[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]

 

[afc171]

If you don't want to transpose the data, just remove the Transpose function:

[COLOR=#000000][FONT=Menlo][COLOR=#011993]If[/COLOR] ActiveCell.Column = 2 [COLOR=#011993]Then[/COLOR][/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Range("B4:E4") = ActiveCell.Resize(, 4)[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]

hmm does not seem to put any data in the cells?

 

[Rick Rothstein]

hmm does not seem to put any data in the cells?

What if you add the Value property call...

Range("B4:E4").Value = ActiveCell.Resize(, 4).Value

 

[afc171]

What if you add the Value property call...

Range("B4:E4").Value = ActiveCell.Resize(, 4).Value

Yes, that is it. Excellent thanks Rick.