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

 

[BiocideJ]

I think this may be what you are looking for. I'm not entirely sure how you're trying to make the row of the copy range variable so I am not doing anything with that, but this uses RESIZE to just make the copy range 1 Row Tall and 3 Columns Wide and simplified your pasting using Excel's built in copy/pastespecial functionality.

Sub copyCells()


Range("B3").Resize(1, 3).Copy
Range("N5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

 

[Eric W]

You can replace that entire macro with:

Range("N5:N7") = WorksheetFunction.Transpose(Range("B3:D3"))

As far as selecting a certain row, how do you want to do it? Ask the user, or pass it to the routine somehow? The line below will move the selected cell and the 2 to the right of it:

Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))

 

[afc171]

Thanks guys

As far as selecting a certain row, how do you want to do it? Ask the user, or pass it to the routine somehow? The line below will move the selected cell and the 2 to the right of it:

Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))

I only want the users to select a cell in the B column just by clicking in it and then showing the cell information plus the 2 cells next to it in cells N5:N7 which the line above you gave works fine, although this will copy the data wherever you click.

 

[Eric W]

You could check if the ActiveCell is in column B:

    If ActiveCell.Column = 2 Then
        Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))
    End If

 

[afc171]

You could check if the ActiveCell is in column B:

    If ActiveCell.Column = 2 Then
        Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))
    End If

Mate that is spot on!

Thanks alot

 

[Eric W]

You're welcome!

 

[afc171]

Eric out of interest how would I get the code to paste across instead of down, i tried the below but keep getting just the first cell and not the next 3 along

[COLOR=#000000][FONT=Menlo]WorksheetFunction.Transpose(ActiveCell.Offset(0))[/FONT][/COLOR]

 

[Rick Rothstein]

Eric out of interest how would I get the code to paste across instead of down, i tried the below but keep getting just the first cell and not the next 3 along

[COLOR=#000000][FONT=Menlo]WorksheetFunction.Transpose(ActiveCell.Offset(0))[/FONT][/COLOR]

You would just use this...

ActiveCell.Resize(, 3)

 

[afc171]

think i confused myself

I was trying to select a cell in column B and then copy the data to B4 to E4, but it keeps giving me the same data in B4 to E4 instead of any cell in column B and the next 3 columns

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

I did it this way which works, but seemed long winded lol

[COLOR=#011993][FONT=Menlo]If[/FONT][/COLOR][COLOR=#000000][FONT=Menlo] ActiveCell.Column = 2 [/FONT][/COLOR][COLOR=#011993][FONT=Menlo]Then[/FONT][/COLOR][COLOR=#000000][FONT=Menlo]        
       Range("b4") = WorksheetFunction.Transpose(ActiveCell.Offset(0))[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Range("c4") = WorksheetFunction.Transpose(ActiveCell.Offset(0, 1))[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Range("d4") = WorksheetFunction.Transpose(ActiveCell.Offset(0, 2))[/FONT][/COLOR]
[COLOR=#000000][FONT=Menlo]        Range("e4") = WorksheetFunction.Transpose(ActiveCell.Offset(0, 3))[/FONT][/COLOR]
[COLOR=#011993][FONT=Menlo]EndIf[/FONT][/COLOR]
[FONT=Menlo]
[/FONT]
[COLOR=#011993][FONT=Menlo]EndSub[/FONT][/COLOR]