Copy only single value from named range of values

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Can you post all the code?
 
Upvote 0
Here you go

VBA Code:
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
 
Upvote 0
That code only copies the value in A2 for me, not the entire named range.
How are you creating the named range?
 
Upvote 0
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...

1644423886439.png
 
Upvote 0
Do you have any other code that affect things?
Also what is in A2, a formula or a value?
 
Upvote 0
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.
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,185
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top