Vba paste special error

[scoha]

I have the following lines which are part of code to copy from a named range (NewName_451) to the next free line of column range (RangeNames451):

Range("NewName_451").Copy Range("c8").End(xlDown).Offset(1,0)

But I don't want to copy the cell format, just the text. How do I modify code so it just pastes the text, tried adding PasteSpecial Paste:=xlPasteValues but get an error message,

 

[jbeaucaire]

Paste special requires two separate vba commands:

Range("NewName_451").Copy 
Range("C8").End(xlDown).Offset(1).PasteSpecial xlPasteValues

 

[Trebor76]

Hi scoha,

As long as there's something in Col C past Row 8 (or else the code will error as it will be trying to go off the page), this will work:

Range("NewName_451").Copy
    Range("C8").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False

HTH

Robert

 

[mirabeau]

If you want to keep it as a one-liner, maybe try:

Range("c8").End(xlDown).Offset(1, 0).Value = Range("NewName_451").Value

 

[scoha]

Great fix mirabeau - much simpler and works well - thanks to others too