VBA - Paste Values only

[wrightyrx7]

Hello all, I have this VBA and wanted to know how I can get it to paste the values only because at the minute it changes the format of the cells on the sheets its being pasted to?

Sub Test()

Cells(ActiveCell.Row, 1).Copy Sheets("Advance Payment").Cells(6, 2)
Cells(ActiveCell.Row, 3).Copy Sheets("Advance Payment").Cells(2, 5)
Cells(ActiveCell.Row, 4).Copy Sheets("Advance Payment").Cells(5, 5)
Cells(ActiveCell.Row, 10).Copy Sheets("Advance Payment").Cells(15, 4)
Cells(ActiveCell.Row, 11).Copy Sheets("Advance Payment").Cells(17, 5)
Cells(ActiveCell.Row, 11).Copy Sheets("Advance Payment").Cells(18, 5)
Cells(ActiveCell.Row, 12).Copy Sheets("Advance Payment").Cells(12, 4)
Cells(ActiveCell.Row, 8).Copy Sheets("Advance Payment").Cells(1, 5)

Sheets("Advance Payment").Activate


End Sub

Thanks
Chris

 

[GTO]

Is the sheet it is copying from changing (and thus needs to be the activesheet)? Or is it always copying from the same sheet?

 

[GTO]

ACK!... Nevermind that last. ActiveCell should have been a clue.

 

[dantheman9]

something like using;

Selection.PasteSpecial Paste:=xlPasteValues

maybe?

 

[njimack]

This looks a lot like you're other post I've been answering. Try this:

Sub a()

Dim i As Long

With Sheets("Advance Payment")
    .Cells(6, 2) = Cells(ActiveCell.Row, 1)
    .Cells(2, 5) = Cells(ActiveCell.Row, 3)
    'etc
    'etc
End With


End Sub

 

[GTO]

Untested - try:

 With ThisWorkbook
Worksheets("Advance Payment").Cells(6, 2).Value = Cells(ActiveCell.Row, 1).Value
Worksheets("Advance Payment").Cells(2, 5).Value = Cells(ActiveCell.Row, 3).Value
Worksheets("Advance Payment").Cells(5, 5).Value = Cells(ActiveCell.Row, 4).Value
 
'...and so on...
End With

 

[wrightyrx7]

Where abouts am i putting this code (im sorry im really new to vba)

Njimack i tried urs like this

Sub Test()
Sheets("Advance Payment").Cells(8, 1) = Cells(ActiveCell.Row, 6) & " " & Cells(ActiveCell.Row, 7)
Cells(ActiveCell.Row, 1).Copy Sheets("Advance Payment").Cells(6, 2)
Cells(ActiveCell.Row, 3).Copy Sheets("Advance Payment").Cells(2, 5)
Cells(ActiveCell.Row, 4).Copy Sheets("Advance Payment").Cells(5, 5)
Cells(ActiveCell.Row, 10).Copy Sheets("Advance Payment").Cells(15, 4)
Cells(ActiveCell.Row, 11).Copy Sheets("Advance Payment").Cells(17, 5)
Cells(ActiveCell.Row, 11).Copy Sheets("Advance Payment").Cells(18, 5)
Cells(ActiveCell.Row, 12).Copy Sheets("Advance Payment").Cells(12, 4)
Cells(ActiveCell.Row, 8).Copy Sheets("Advance Payment").Cells(1, 5)


Dim i As Long

With Sheets("Advance Payment")

    .Cells(6, 2) = Cells(ActiveCell.Row, 1)
    .Cells(2, 5) = Cells(ActiveCell.Row, 3)
    .Cells(5, 5) = Cells(ActiveCell.Row, 4)
    .Cells(15, 4) = Cells(ActiveCell.Row, 10)
    .Cells(17, 5) = Cells(ActiveCell.Row, 11)
    .Cells(18, 5) = Cells(ActiveCell.Row, 11)
    .Cells(12, 4) = Cells(ActiveCell.Row, 12)
    .Cells(1, 5) = Cells(ActiveCell.Row, 8)
End With
Sheets("Advance Payment").Activate

End Sub

But still overwriting the format on the Advance Payment worksheet

This looks a lot like you're other post I've been answering. Try this:

Yes it is, i didnt want to bother you any more thoguth i would start doing your head in lol


Thanks for the replies guys think i just need a little more guidance please.

Chris

 

[wrightyrx7]

Figured it out, all by my own self!!! haha

Thanks guys you been a great help.