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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

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,685

ADVERTISEMENT

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

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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,526

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,499
Messages
5,832,068
Members
430,109
Latest member
tinezi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top