Copy range and paste into non contiguous range

Hi all,

I am trying to copy a range from one row and paste those values into a non contiguous range. To clarify, I want to do the exact opposite of the code I wrote below where I copy data from a non contiguous cell range and paste on another sheet in one row. I just started with VBA this week and I have no idea how to do it.

This code works like a charm:

Please Login or Register  to view this content.

Now I want to do the opposite and paste a certain range from the SavedSpecs tab into the range Worksheets("Cover").Range("D3:D6, H3:H6, K3:K5, D8:D10, H8:H10, K8:K9, D13:F14").


I tried the code below but that gives a runtime error 1004. Alternatively I can copy the values for each cell at the time but I quickly realized that will be way too slow.

Please Login or Register  to view this content.

I hope someone can push me in the right direction.

Hi amros

Did not use your code....Try this.

Please Login or Register  to view this content.

I am sure there is a simpler method but just can't wrap my head around it...
Loop 1 - loops through the different Ranges in Array
Loop 2 - loops through each cell in the Range

Well there you have it...the Master has spoken....Thanks for the lesson jindon

Edited:
@ jindon....Please explain

Please Login or Register  to view this content.

Edited2:
Nevermind...got it

Edited3:
Nope not

Thanks a lot, shorter code but seems a little slower than Sintek's. The range that I need to copy the values too will be expanded to roughly 300 cells.
I just tested with 50 values and that takes roughly 7 seconds.

Is there any way to optimize the code for speed?

-- Edit --

Saving the spec, so doing the opposite is very fast. Is there a way to give the copy command and paste all values at the same time? I think there must be way to make it faster?

Add this

Please Login or Register  to view this content.

before and set to true after

Hereby amended as per jindon suggestion...

Please Login or Register  to view this content.

Happy to keep contributing and thereby learning. Thanks for rep + added

Think it is time for a upload of your workbook....or sample thereof...

Originally Posted by Pete_UK
To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

File is destroyed a bit as content is sensitive.
What I want here is to 'Save' (Copy) data from Cover sheet, all the grey cells, to the SavedSpecs sheet. When this is done properly I want to continue by having the options to load (Copy) specifications from SavedSpecs onto the Cover sheet.

Nope invalid attachment...

Your code works without errors

Hi amos

Use this...removed spaces between ","

Please Login or Register  to view this content.

Please Login or Register  to view this content.