Copy only single value from named range of values

[GeeWhiz7]

I am using O365 and trying to copy a single value from inside a named range of values.

When I use Range.Copy even if it references a single cell (example: Range("A2").Copy ), the whole named range that fills that column (example A1:A50 = _Range1) comes with it to the destination.
Is there a way to only copy a single value?

 

[Fluff]

Can you post all the code?

 

[GeeWhiz7]

Here you go

Sub CaptLoopResults()
 
Dim addRow As Long
'Dim aMin As Date
'Dim aMax As Date
 
DoEvents
 
Application.ScreenUpdating = False

     addRow = Sheet1.Cells(Rows.Count, "A").End(xlUp).row
     
     Sheet4.Range("C18").Copy 'Date
     Sheet1.Range("A" & addRow + 1).PasteSpecial Paste:=xlPasteValues
     
     Sheet4.Range("B2").Copy
     Sheet1.Range("B" & addRow + 1).PasteSpecial Paste:=xlPasteValues 'name
     
     Sheet4.Range("H196").Copy
     Sheet1.Range("C" & addRow + 1).PasteSpecial Paste:=xlPasteValues  'Total

'This is where I'm stuck.
'I'm trying to extract only the first value in a cell that is part of a named range , and the last.
'Range name is _Total and that location for the first value is A2, last value A1276
'When I copy, the whole named range comes with it instead.
   
     Sheet4.Range("A2").Copy
     Sheet1.Range("D" & addRow + 1).PasteSpecial Paste:=xlPasteValues  'Range Start Value

     Sheet4.Range("A1276").Copy
     Sheet1.Range("E" & addRow + 1).PasteSpecial Paste:=xlPasteValues  'Range End Value
     
Application.ScreenUpdating = True
   
End Sub

 

[Fluff]

That code only copies the value in A2 for me, not the entire named range.
How are you creating the named range?

 

[GeeWhiz7]

That code only copies the value in A2 for me, not the entire named range.
How are you creating the named range?

I originally named it using the drop down, top left of the UI.

Below is what the result looks like for me, you can see that in the Start column, I do get the single value at the top, but then I get the rest of the named range with it...

 

[Fluff]

Do you have any other code that affect things?
Also what is in A2, a formula or a value?

 

[GeeWhiz7]

Do you have any other code that affect things?
Also what is in A2, a formula or a value?

Nothing that affects this sheet except this macro. The named ranges are manipulated on numerous other sheets and macros, but this should be testable as an island.
A2 is a Date.

I did figure out a simple workaround, by assigning another set of cells the formulas
-MIN(_Named Range culprit)
-MAX(_Named Range culprit)
and then doing the vba copy on those cells and that returns just the single value.

I don't want to take too much of your valuable time and appreciate your help.

 

[Fluff]

Glad you sorted it & thanks for the feedback.