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

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?
cashonlyAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Try

With Worksheets("Sheet1")
    .Range("D1:D5").value = .Range("C1:C5").value
End With
0
 
dsackerContract ERP Admin/ConsultantCommented:
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
 
aikimarkCommented:
The ssaqibh comment http:#a39187966 is the right way to do it.
0
 
cashonlyAuthor Commented:
Worked great!
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.

All Courses

From novice to tech pro — start learning today.