Printable View
I am trying to copy data from one sheet to another - but get a runtime error 1004 --- it will not allow me to have more than a single range --- any ideas or do I have to copy/paste one range at a time
My range is i.e. "$A4:$A299,$K4:$K10"
Code:Worksheets(SourceSheetName).Range(RangeSource).Copy
Worksheets(DistinationSheetName).Paste Destination:=Worksheets(DistinationSheetName).Range(RangeDestination)
Hi
I think the range size should be same otherwise you won't able to paste.
When you copy/paste, the range must be contiguous. If you try to copy/paste non-contiguous ranges manually, you get a warning message that says "That command cannot be used on multiple selections."
If you actually want all the properties of the cells copied along with the values in the cells, you will have to loop through the range's areas (basically, an area is a contiguous sub-range within the overall non-contiguous range). Something like this...
I can't give you specifics for the part I enclosed in the double-curly braces because you gave us no information about RangeDestination, but you would have to calculate each top left cell in it for each sub-range you are copying. If you provide more details, we can give you the actual code you would use.Code:For Each Rng In Worksheets(SourceSheetName).Range(RangeSource).Areas
Rng.Copy {{reference to the top left cell where the sub-range is to be copied to}}
Next
Rick
Thanks --- I have changed my code so it loops -- one range at a time-- but what a pain - I have to copy columns that are not contiguous -- also my destination is not the same columns --- I appreciate the offer of code. But I got it working now. I have approx 700 columns of data --- so I am considering to insert a row and then place a number in that row represent the order that I want to copy the data --- I know how to sort rows -- But have never sorted columns. I will make a separate post asking how to sort on rows
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
If you provide the details of your setup, what ranges comprise your non-contiguous areas, why/how you identify them, what you are doing with them, etc., etc., then perhaps we can offer some more condensed coding than it sounds like you may have currently. Also posting the code you have now, in conjunction with the above details, might help us help you further as well. VB coding can do marvelous things, and in a lot of cases, in sometimes surprisingly compact code procedures, but for anyone to help you to do that, we need details.Quote:
Originally Posted by Rasm