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
320 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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