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?

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

Hope that helps

Strud

Please Login or Register  to view this content.

These two lines will show errors

Please Login or Register  to view this content.

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