change code from paste to paste special

[jek40]

I would like to do a paste special values rather than a paste.
I have tried every change in the code i could locate but have had no luck so far.

other than making this change the code is working great so i would like to make minimal changes to the code.

Appreciate any help
Thanks
John

[/code]
Sub Macro1()
Dim myRange As Range, cell As Range, PasteRange As Range
Sheets("cola data").Select
Set myRange = Range("g3:g10")

For Each cell In myRange
If Range("h7") <> "" Then
Set PasteRange = Range("h65536").End(xlUp).Offset(1, 0)
Else
Set PasteRange = Range("h7")
End If
If "na" = (cell.Value) Then
Range(Cells(cell.Row, "d").Address, _
Cells(cell.Row, "f").Address).Copy Destination:=PasteRange
End If
Next cell
End Sub

 

[GlennUK]

Have you looked at the help for PasteSpecial?

 

[rs2k]

Have you tried recording a macro with paste special values and then adapt it to your code to suit?

Colin.

 

[jek40]

I have looked but have not found the correct way to incorporate it into this code

thanks
John

 

[GlennUK]

Exactly what is it about PasteSpecial that you don't understand?

 

[al_b_cnu]

Of course, you dont HAVE to use Pastespecial, try this (untested) code:

Sub Macro1()
Dim cell As Range
Dim lRow As Long, iCol As Integer, vData(1 To 3) As Variant
Sheets("cola data").Select

lRow = Range("h65536").End(xlUp).Row + 1
If lRow < 7 Then lRow = 7

For Each cell In Range("g3:g10")
    If "na" = (cell.Value) Then
        For iCol = 1 To 3
            vData(iCol) = Cells(cell.Row, iCol + 3).Value
        Next iCol
        Range("H" & cell.Row & ":J" & cell.Row).Value = vData
    End If
Next cell
End Sub

 

[al_b_cnu]

Erm maybe this will work better:

Sub Macro1()
Dim cell As Range
Dim lRow As Long, iCol As Integer, vData(1 To 3) As Variant
Sheets("cola data").Select

lRow = Range("h65536").End(xlUp).Row + 1
If lRow < 7 Then lRow = 7

For Each cell In Range("g3:g10")
    If "na" = (cell.Value) Then
        For iCol = 1 To 3
            vData(iCol) = Cells(cell.Row, iCol + 3).Value
        Next iCol
        Range("H" & lRow & ":J" & lRow).Value = vData
        lRow = lRow + 1
    End If
Next cell
End Sub

 

[jek40]

thanks al

It works great

I am trying to understand the code as I may need to adapt it for other uses.

If I wanted to change the data copied from columns e f and g to b through g and then paste starting at k what changes would I have to make to accomplish this

Thanks
JOhn

I am using your second suggestion

 

[jek40]

Ok al I believe I have it

Thanks for your help

John