Solved

In Excel VBA, how can I do a copy and a pastespecial WITHOUT using the Windows copy buffer?

Posted on 2013-05-22
4
317 Views
Last Modified: 2013-05-22
My problem is that when I use code that does a copy and then a PasteSpecial in Excel, while that code is running (ie: in a loop), I can't use the copy buffer in my system for anything else as it gets replaced with the data from the Excel copy.  This happens when I use code like this:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1:D5").PasteSpecial _
        Paste:=xlPasteValues
End With

Open in new window


I have no problem if I do a single statement copy in Excel VBA with code like this:

With Worksheets("Sheet1")
    call .Range("C1:C5").Copy(.Range("D1:D5"))
End With

Open in new window


But doing it that way, I can't do a paste special which I need so that I can replace the formulas with values.

How can I get around this problem?
0
Comment
Question by:cashonly
4 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39187966
Try

With Worksheets("Sheet1")
    .Range("D1:D5").value = .Range("C1:C5").value
End With
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39187968
Since  you're placing the values in column D from the formulas in column C, you could simply do this:
    Dim row As Integer
    For row = 1 To 5
        Cells(row, 4).Value = Cells(row, 3).Text
    Next row

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
ID: 39189705
The ssaqibh comment http:#a39187966 is the right way to do it.
0
 

Author Closing Comment

by:cashonly
ID: 39189721
Worked great!
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now