Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-25-2021, 11:22 PM
Marcia's Avatar
Marcia Marcia is offline Paste value of selected cell to adjacent cell Windows 10 Paste value of selected cell to adjacent cell Office 2019
Expert
Paste value of selected cell to adjacent cell
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default Paste value of selected cell to adjacent cell

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.
Attached Files
File Type: xlsm vbaCopy.xlsm (15.5 KB, 6 views)
Reply With Quote
  #2  
Old 08-26-2021, 12:25 AM
Purfleet Purfleet is offline Paste value of selected cell to adjacent cell Windows 10 Paste value of selected cell to adjacent cell Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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
Reply With Quote
  #3  
Old 08-26-2021, 12:28 AM
Guessed's Avatar
Guessed Guessed is offline Paste value of selected cell to adjacent cell Windows 10 Paste value of selected cell to adjacent cell Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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.
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia

Last edited by Guessed; 08-26-2021 at 12:31 AM. Reason: Purfleet's solution got there first
Reply With Quote
  #4  
Old 08-26-2021, 12:55 AM
Marcia's Avatar
Marcia Marcia is offline Paste value of selected cell to adjacent cell Windows 10 Paste value of selected cell to adjacent cell Office 2019
Expert
Paste value of selected cell to adjacent cell
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste value of selected cell to adjacent cell How to paste the value of a selected cell to another sheet? Learner7 Excel Programming 3 06-30-2019 10:35 PM
Paste value of selected cell to adjacent cell 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
Paste value of selected cell to adjacent cell 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

Other Forums: Access Forums

All times are GMT -7. The time now is 01:54 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft