Copying/Pasting cell ranges

Welcome to the forum

There is no need to select if all you are doing is extracting or writing values
- just refer to the sheet and range
- selecting slows your code down

The input boxes allow you to click on a cell to select destination, so activating correct sheet in advance is best here

"Activate" a sheet (or workbook) and "Select" a range generally works (but it does depend on what you are doing)
- activating moves the focus to the object
- only one object can be active, but multiple objects can be selected

Please Login or Register  to view this content.

Use loops and avoid duplicating code:

Please Login or Register  to view this content.

You could condense your code like this:

Please Login or Register  to view this content.

Thankyou so much for the reply Kev - which works. Unfortunately, I've since realised the row being copied from can change also.
To re-iterate - User will be copying data from TWO cell ranges from one worksheet and pasting them into another.
Each range consists of 4 adjacent cells (on same row), but the 1st cell of range 2 is always 9 cells to the right of 1st cell of Range 1
It is ONLY the ROWS where the data is copied from AND pasted to, that can change. So I assume macro would only need user input as to where the starting cells are for copying and pasting? i.e D3 and B5 respectively, for eg: copy from D3:G3 and M1:P1 (Report worksheet), then paste to B5:E5 and J5:M5 (numbers). Hope this is clear for anyone who might want to help further.

So I assume macro would only need user input as to where the starting cells are for copying and pasting? i.e D3 and B5 respectively, for eg: copy from D3:G3 and M1:P1 (Report worksheet), then paste to B5:E5 and J5:M5 (numbers). Hope this is clear for anyone who might want to help further.

msgbox.jpg

Assuming that the offsets in both cases are consistent, this method should work for you
As you can see it matches exactly what you asked for when D3 and B5 selected
I added a function to return range addresses which you may find useful again

Please Login or Register  to view this content.

You are welcome.
Please mark thread SOLVED
(See thread tools @ top of thread)