vba copy value returns #N/A

[martipe1]

I'm trying to copy data and paste the values but when pasting formulas I get #N/A instead of the result of the formula.

The code to paste value:

ReporteWks.Range("A5:O" & rowsCopy).Value = rngCopy.Value

The formulas are a mix of several IFs with INDEX/Match, the results expected are either blank or a value, I have no problem with the formulas but I need to copy values, not formulas to another worksheet.

What can I do?

Thanks.

 

[Joe4]

What is the value if "rowsCopy"?
What range is "rngCopy" set to?

If you are doing multiple value cells at once, try using Copy --> Paste Special --> Values instead (can be done via VBA).

 

[martipe1]

Thank you for your answer.

rowsCopy = rngCopiy.Rows.Count
Set rngCopy = TempWks.Range("ReportDataCopy")

Named range
=OFFSET(Report!$AA$1,1,0,COUNTA(Report!$AK:$AK)-1,11)

As far as I know, the only at to paste special within is to set the target range value to range source value. is there another way?

 

[Joe4]

One way to do it is something like this:

'   Select range to copy
    Sheets("Sheet1").Range("C1:C3").Copy

'   Go to next sheet
    Sheets("Sheet2").Select
    
'   Select first cell to paste to and paste values
    Range("G1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

I documented the code to tell you what each of the three steps does.
You can substitute range variables for any of the ranges used in the code.

 

[Fluff]

Your RngCopy is only 11 columns wide, but you are trying to copy that to a range 15 columns wide.

 

[martipe1]

Your RngCopy is only 11 columns wide, but you are trying to copy that to a range 15 columns wide.

Duh!! You are absolutely right.

Thanks for your help!!

 

[Fluff]

Glad we could help & thanks for the feedback.