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

afc171

Board Regular
Joined
Jan 14, 2017
Messages
143
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
[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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.

Code:
Sub copyCells()


Range("B3").Resize(1, 3).Copy
Range("N5").PasteSpecial Paste:=xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub
 
Upvote 0
You can replace that entire macro with:

Code:
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:

Code:
Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))
 
Upvote 0
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:

Code:
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.
 
Upvote 0
You could check if the ActiveCell is in column B:

Code:
    If ActiveCell.Column = 2 Then
        Range("N5:N7") = WorksheetFunction.Transpose(ActiveCell.Resize(, 3))
    End If
 
Upvote 0
You could check if the ActiveCell is in column B:

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

Mate that is spot on!

Thanks alot :)
 
Upvote 0
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

Code:
[COLOR=#000000][FONT=Menlo]WorksheetFunction.Transpose(ActiveCell.Offset(0))[/FONT][/COLOR]
 
Last edited:
Upvote 0
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

Code:
[COLOR=#000000][FONT=Menlo]WorksheetFunction.Transpose(ActiveCell.Offset(0))[/FONT][/COLOR]
You would just use this...

ActiveCell.Resize(, 3)
 
Upvote 0
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


Code:
[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

Code:
[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]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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