copy data values in a Name Range to a new worksheet

[7thMonth]

I want to in same workbook.
The name range is called ReportCopy and the range formula is a dynamic =OFFSET('C_Register'!$H$1,,,COUNTA('C Register'!$H:$H),6)

The Macro I am trying to us is: '
'
Sheets("C_Register").Select
Range("ReportCopy").
Selection.Copy
Sheets("Report").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

My goal is to save the worksheet as a csv file to be distributed.