Paste value of selected cell to adjacent cell

Marcia · #1 08-25-2021, 11:22 PM

Hi. My macro creation level is limited to recording. I created a code by recording the steps in copying the value of A2 then pasted it in B2. When I selected A16 then ran the macro, the value in A16 was pasted in B2. It should be copied to B16 which is the adjacent cell of A16. How do I edit the line:
Range ("B2").Select?

Code:

Sub Copy()
    Selection.Copy
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Thank you.

Purfleet · #2 08-26-2021, 12:25 AM

try this, it will paste whatever is copied one cell to the right - no more validation so it will keep doing it if you press the button again and again. Can be improved further

Code:

Sub Copy()
    Selection.Copy
    Selection.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
End Sub

Guessed · #3 08-26-2021, 12:28 AM

There is probably a more elegant way but this appears to work

Code:

Sub CopyBeside()
  Dim aCell As Range
  For Each aCell In Selection
    aCell.Copy Destination:=aCell.Offset(0, 1)
  Next aCell
End Sub

And there we have another way courtesy of Purfleet - note there will likely be a difference with how a discontinuous selection works with these two alternative approaches.

Marcia · #4 08-26-2021, 12:55 AM

It's perfect, Purfleet.
Andrew, your code copied the selected cell to the right. There is a formula in col A that joins Col B and C. The macro then does the job of pasting the value in Col A to col B. I have saved your code for future reference.
Thank you all.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to paste the value of a selected cell to another sheet?	Learner7	Excel Programming	3	06-30-2019 10:35 PM
Fill blank cell with value from adjacent cell	kevinbradley57	Excel Programming	2	04-17-2018 08:40 AM
IF adjacent cell empty Copy from Other Column	ChrisOK	Excel Programming	8	03-08-2018 09:17 PM
Increase number in cell, based on value in adjacent cell	scottyb	Excel	3	02-02-2017 03:51 AM
How to conditionally format cells in Col. A if it matches adjacent cell in Col. B?	alshcover	Excel	2	06-03-2014 12:50 PM