Paste Value Macro Help

[tdixon]

I am currently working on a macro to move specific rows of data from various sheets in a workbook onto a data sheet in the same workbook.

I'm using the below code that I lifted from an old post and modified slightly;

Sub cpynpst()
Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
Set sh4 = ActiveSheet
Set sh5 = Sheets("Data Sheet")
lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("9:20")
rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

The issue I am having is that I need the data to be copied to the data sheet as values (i.e. paste special, paste values) and the macro currently copies the data formulas and all...

I am new to VBA and unfortunately lake the savvy to resolve this and would very much appreciate some help!

 

[njimack]

Welcome to the board. Change this:

rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)

to this:

rng.EntireRow.Copy 
sh5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlValues

 

[mumps]

Try:

Sub cpynpst()
    Dim sh4 As Worksheet, sh5 As Worksheet, lr As Long, rng As Range
    Set sh4 = ActiveSheet
    Set sh5 = Sheets("Data Sheet")
    lr = sh4.Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = sh4.Range("9:20")
    rng.EntireRow.Copy
    sh5.Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues
End Sub

 

[tdixon]

That's brilliant,

Works perfectly!

Thanks very much