Range.Copy (using Named Ranges) returns Formulas instead of Values (Solved)-CodePudding

I am trying to improve the performance of a couple of my Report Form which take several (5 to 10) seconds to complete loading the information (this is down from a previous time of 15 to 20 seconds). While doing research I found multiple threads discussing the processing time advantages of using the Range.Copy Method verses reading and writing one row of a range at a time.

My original code (one row at time) works perfectly except for the Time Factor and is as follows:

'Begin loading wsCost5Yr Worksheet
For frmYr = sYear To wbCurYear
  Debug.Print locID, frmYr
    wsSum.Cells(3, "Q") = frmYr 'Cell Q3 of the Summary Worksheet
    chartYrsCost.Cells(1, uClmn) = frmYr
        For uRow = 1 To 12
          'Get Gas Cost Values
            costG = sumCostG.Cells(uRow, 1).Value2
          'Get Electric cost
            costE = sumCostE.Cells(uRow, 1).Value2
            Debug.Print costG, costE
          'Convert Zero Values to Null
          If Not costE = 0 Then
                cmp5YrCostEdata.Cells(uRow, uClmn) = costE
            Else
                cmp5YrCostEdata.Cells(uRow, uClmn) = vbNullString
          End If
          If Not costG = 0 Then
                cmp5YrCostGdata.Cells(uRow, uClmn) = costG
            Else
                cmp5YrCostGdata.Cells(uRow, uClmn) = vbNullString
          End If
        Next uRow
    uClmn = uClmn   1
Next frmYr

ALL ranges are properly Declared and Set during Initialization. The new code I am trying to implement, using the Range.Copy method is as follows:

'Begin loading wsCost5Yr Worksheet
For frmYr = sYear To wbCurYear
  Debug.Print locID, frmYr
    wsSum.Cells(3, "Q") = frmYr 'Cell Q3 of the Summary Worksheet
   
    chartYrsCost.Cells(1, uClmn) = frmYr
    sumCostG.copy cmp5YrCostGdata.Cells(1, uClmn)
    sumCostE.copy cmp5YrCostEdata.Cells(1, uClmn)
        For uRow = 1 To 12
          'Get Gas Cost Values
            costG = cmp5YrCostGdata.Cells(uRow, uClmn).Value2
          'Get Electric cost
            costE = cmp5YrCostEdata.Cells(uRow, uClmn).Value2
            Debug.Print costG, costE
          'Convert Zero Values to Null
          If Not costE = 0 Then cmp5YrCostEdata.Cells(uRow, uClmn) = vbNullString

          If Not costG = 0 Then cmp5YrCostGdata.Cells(uRow, uClmn) = vbNullString

        Next uRow
    uClmn = uClmn   1
Next frmYr

Both ranges, sumCostG and sumCostEcontain formulas that perform calculations as described above and and when I look ad the written contents of the destination cell I find #REF! errors with the actual cell contents as =IF(A20="",#REF!,SUM(#REF!-A20))!

I have read over 20 threads dealing with the Range.Copy method and the more I read the more confused I become. I'm Certain it's something simple that I am missing! Can someone Please Help me with this issue?

Thankyou in advance for your assistance.

CodePudding user response：

Change this:

    sumCostG.copy cmp5YrCostGdata.Cells(1, uClmn)
    sumCostE.copy cmp5YrCostEdata.Cells(1, uClmn)

to this:

    sumCostG.copy 
    cmp5YrCostGdata.Cells(1, uClmn).PasteSpecial xlPasteValues
    sumCostE.copy 
    cmp5YrCostEdata.Cells(1, uClmn).PasteSpecial xlPasteValues

Another option is destination.value = source.value:

    cmp5YrCostGdata.Cells(1, uClmn).Resize(sumCostG.Rows.Count-1,sumCostG.Columns.Count-1).Value = sumCostG.Value
    cmp5YrCostEdata.Cells(1, uClmn).Resize(sumCostE.Rows.Count-1,sumCostE.Columns.Count-1).Value = sumCostE.Value