Copy and paste range as values to specific sheet/range

Hi all,
I am trying to write some VBA to copy and paste a specific range in the active sheet to another sheet again to a specific range, but as values (as I have formatting rules set up in the destination).

Range for copy: J4:J34
Sheet copying from: "U4Brief1"

Range to paste to B4:B34
Sheet pasting to: "U4 Tracker"

I want to assign this to a button for colleagues to click to copy the data.

Any advice greatly appreciated.

Thanks

Luke

If it's a specific range like that then you may be best using the 'Record Macro' feature under the developer tab. Then just copy and paste it as needed and you will have a code.

Then you can see how it works...

If this unsuitable or the range to copy might change over time then I can give you a code for this...

Strud

Hi Strud,
Many thanks for your quick reply. I need to replicate the code over ten sheets, so any code that I could tweak the source sheet/range would be very very helpful. This is what I have from the macro, does this look right?

Sub u4b1copy()
    Range("J4:L34").Select
    Selection.Copy
    Sheets("U4 Tracker").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Yeah that looks fine. The code can be shortened a bit but if it works it works.

If by replicate you mean copy to more sheets then all you would have to us add an extra paste bit in. So assuming your third sheet is 'Sheet3' then it would be:

Sub u4b1copy()
    Range("J4:L34").Select
    Selection.Copy
    Sheets("U4 Tracker").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet3").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Hope that helps

Strud

Sub u4b1copy()
    Range("J4:L34").Copy
    Sheets("U4 Tracker").Range("B4").PasteSpecial xlValues
    Sheets("Sheet3").Range("B4").Copy
    Sheets("U4 Tracker").Range("B4").PasteSpecial xlValues
    Application.CutCopyMode = 0
End Sub

These two lines will show errors

Sheets("Sheet3").Select
    Range("B4").Select

Meant to be copy
I have assumed that the first line
Range("J4:L34").Select
refers to active sheet.

Hi Luke, AB33's response is correct that was my mistake. Try adjusting it as they suggested and you should see no error message.

Apologies.

Strud