change code from paste to paste special

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

rs2k

Well-known Member
Joined
Aug 15, 2004
Messages
1,413
Have you tried recording a macro with paste special values and then adapt it to your code to suit?

Colin.
 

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
I have looked but have not found the correct way to incorporate it into this code

thanks
John
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402

ADVERTISEMENT

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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Of course, you dont HAVE to use Pastespecial, try this (untested) code:
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

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

Erm maybe this will work better:
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" & lRow & ":J" & lRow).Value = vData
        lRow = lRow + 1
    End If
Next cell
End Sub
 

jek40

Active Member
Joined
Jan 17, 2005
Messages
317
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
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,488
Members
412,669
Latest member
nickyon1
Top