Why does my xlPasteValues past blanks?

[Brian48072]

Hi,

I have searched the web and this forum for the proper syntax but nothing has worked for me thus far.

I dim and set 2 ranges, and do a copy and paste from one to the other:

Exp314a.Copy
Exp314b.PasteSpecial

When I use the above code, I can see the formulas in the cells, but when I use any of the following code to paste values instead of formulas, I get blank cells instead:

Exp314b.PasteSpecial :=xlPasteValues

Exp314b.PasteSpecial xlPasteValues

Exp314b.PasteSpecial (xlPasteValues)

Can you point me in the right direction?

Thanks.

Brian

[kbkumar]

Hi Brian,

Welcome to the forum, There could be quite a few reasons for this, Can you share the entire code please?

Thanks.

[Moo the Dog]

Have you tried this:

Exp314b.PasteSpecial Paste:=xlPasteValues

- Moo

[Richard Buttrey]

Hi Brian, and welcome to the forum.

Your third option works perfectly well for me. Perhaps you'd better upload your workbook so we can see your request in context.

[Brian48072]

Thanks for the response kbkumar.

Here is the code:

Dim Exp314a As Range
Set Exp314a = Range(Cells(2, 28), Cells(2, 28))

Exp314a.FormulaR1C1 = _
"=IF(RC[7]="""","""",IF(RC[7]=""BP"",RC[-1],VLOOKUP(RC[-1],Export4A!R2C4:R500C5,2,FALSE)))"

'Please note: I only close the Export4A file referred to in the Vlookup after I attempt the paste to values. (I always leave it open when I do just a paste so i can see the formulas).



Dim Exp314b As Range
Set Exp314b = Range(Cells(3, 28), Cells(LastRowB, 28))

Exp314a.Copy
Exp314b.PasteSpecial (xlPasteValues)


'.PasteSpecial xlPasteValues
'.PasteSpecial (xlPasteValues)
''PasteSpecial:=xlPasteValues




'Exp314b.PasteSpecial
'.PasteSpecial

[Brian48072]

Hi Moo,

I still get blanks with Exp314b.PasteSpecial Paste:=xlPasteValues

Brian

[Brian48072]

Hi Richard,

Is this the version that worked for you: Exp314b.PasteSpecial (xlPasteValues) ?

Brian

[kbkumar]

Changing the paste line to:

Exp314b.PasteSpecial Paste:=xlPasteValues

(as also advised by Moo) seems to work fine.

May be you need to upload your workbook to have a closer look....

[Brian48072]

How do I upload my workbook?

[kbkumar]

To attach a file, click on "Go Advanced" below the post and the click on Attachments, browse to the required file, and then push the Upload button.

[Brian48072]

Thanks, I tried to upload the workbook, but even with it zipped it is over 4 mb's. I guess this is the end of the trail. Thanks to everyone who tried to help me.

Brian

[JosephP]

check the sheet before the paste special step happens-are the formulas evaluating to ""?

[Brian48072]

Joseph,

When I just use:

Exp314a.Copy
Exp314b.PasteSpecial

I see the formulas populate all cells in the target range and produce the proper values, including blanks in some but not all cases.

Brian

[JosephP]

do the formulas work in the source range? those are the values you're pasting not the values the formulas would have if you pasted them into the target range. if that's what you need you have to paste the formulas then copy those and paste special values

[Brian48072]

Yes, the formula works in the source range, which is the first cell below the column header.

Then when I use code to copy the source down to the bottom of the same column, the formulas all appear and evaluate properly. But when I try to use paste values, I get all blanks other than in the source cell.

[JosephP]

if you copy one cell and paste special values down the column you will just get the value of that cell in every cell in the column

[Brian48072]

Yes Joseph - you are right. I coded a subsequent Copy-Paste-Special-Values on the entire range after the initial Copy-Paste that populated the range with formulas, and it worked. I couldn't see that myself until you mentioned it.

Thanks so much!

Brian

[JosephP]

you're welcome :-)

please don't forget to mark the thread solved