Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1219
  • Last Modified:

Excel VBA - Copy / Paste without using "selection"

I want to copy values of one row onto another row without "selecting"

Here's my attempt:

Range(vSourceRow).Copy Destination:=Range("2:2").PasteSpecial (xlPasteValues)
0
AndresHernando
Asked:
AndresHernando
  • 5
  • 4
  • 4
  • +1
2 Solutions
 
CluskittCommented:
Assuming vSourceRow is a full row:

Range(vSourceRow).Copy
Range("2:2").PasteSpecial (xlPasteValues)
0
 
imaki06Commented:
Have you tried to make the macro with recoder?

It would give you something like this:
Range("A1:B2").Select
    Selection.Copy
    Range("A4").Select
    ActiveSheet.Paste
0
 
CluskittCommented:
Have you tried reading the question? He specifically said he wanted to copy/paste without selecting.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Rory ArchibaldCommented:
You could also simply assign the .Value property from one range to the other.
0
 
AndresHernandoAuthor Commented:
rorya,
what's the script for your suggestion?  What I wrote below doesn't work.
    Range("SourceRow").Value = Range("DestinationRow").Value

Thanks, --Andres
0
 
Rory ArchibaldCommented:
Other way round:
Range("DestinationRow").Value = Range("SourceRow").Value

Open in new window

as you are assigning the source values to the destination. Note that the ranges must be the same size.
0
 
CluskittCommented:
If Source and Destination are variables, you need to:

Range(DestinationRow).Value = Range(SourceRow).Value
0
 
AndresHernandoAuthor Commented:
Cluskitt:
I tried your suggestion (see below), but it bugs out.  What am I doing wrong?

Sub CopyRows()
    Dim vSourceRow As Range
    Dim vRowNum As Long
   
    vRowNum = 9
    Set vSourceRow = Range(vRowNum & ":" & vRowNum)
    Range(vSourceRow).Copy '..............................................Bugs out here
    Range("2:2").PasteSpecial (xlPasteValues)

End Sub


Tnx, --Andres
0
 
Rory ArchibaldCommented:
vSourceRow is a Range, not an address, so:
vSourceRow.Copy
    Range("2:2").PasteSpecial xlPasteValues

Open in new window

0
 
CluskittCommented:
Yes, it all depends on the the variables you declare. If you declare it as a range, you have to use it as a range. For example, if you declared vSourceRow as String, then assigned it to: "9:9", you could then use Range(vSourceRow). If it's a range, you have to use it as rorya said. If you use it as integer and assign it the value 9, you have to use: Range(vSourceRow & ":" & vSourceRow)
0
 
AndresHernandoAuthor Commented:
Thanks for the info.  Both worked.
To be fair, I split, giving the extra 5pts to Cluskitt for answering first.

I'm not sure if this is the correct protocol... I just recently started using this site.

Please tell me if I am in breach of proper etiquette by splitting pts like I did.
0
 
Rory ArchibaldCommented:
To my mind, you closed this properly - thank you! :)
0
 
CluskittCommented:
I agree. Both answered the question using different approaches. Not that points are that important, but it's only fair to split things :)
0
 
AndresHernandoAuthor Commented:
Great.  Thanks again guys.  --Andres
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now