• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can we copy paste certain noncontinous ranges from one excel file to another

C

c_rahul001

New Member
I have some ranges below which i want to copy from one file and paste to another file.

as you can see the ranges are not continuos but it wil be ok if the output will be in continous range.

Range("C2021:G2027,C2518:G2531,C2617:G2629,C2655:G2659")

Range("J2021:Q2027,J2518:Q2531,J2617:Q2629,J2655:Q2659")

Range("U2021:U2027,U2518:U2531,U2617:U2629,U2655:U2659")

Range("W2021:W2027,W2518:W2531,W2617:W2629,W2655:W2659")


There are 4 different ranges which we will need to copy paste one by one to another file. Can we do so usin macro.
 
Thankyou Bobhc for you help.


I have 70 to 80 ranges for which i need macro. Now i am using same Ctrl copy. But it is taking time i want same to be automated. And we cant select all the ranges at a time unable to do so as excel limitation. We have to select ranges one by one such as first Range("C2021:G2027,C2518:G2531,C2617:G2629,C2655:G2659")

then copy paste into other file again come back to orignal file copy paste second Range("J2021:Q2027,J2518:Q2531,J2617:Q2629,J2655:Q2659") paste to file and so on. Hence need a macro.My ranges change every day only by a row. so i want to automate the process.


Could you help me in same.
 
Hi, c_rahul001!


Give a look to this code:

-----

[pre]
Code: 
Option Explicit

Sub CopyMultipleRangesTogether()
' constants
Const ksWSInput = "Hoja1"
Const ksInputRange1 = "A1:C2"
Const ksInputRange2 = "C4:E9"
Const ksInputRange3 = "I2:J5"
Const ksWSOutput = "Hoja2"
Const ksOutputRange1 = "A1"
Const ksOutputRange2 = "E1"
Const ksOutputRange3 = "H1"
' declarations
' start
' process
Worksheets(ksWSInput).Range(ksInputRange1).Copy Worksheets(ksWSOutput).Range(ksOutputRange1)
Worksheets(ksWSInput).Range(ksInputRange2).Copy Worksheets(ksWSOutput).Range(ksOutputRange2)
Worksheets(ksWSInput).Range(ksInputRange3).Copy Worksheets(ksWSOutput).Range(ksOutputRange3)
' end
End Sub
[/pre]
-----


It copies 3 ranges to another worksheets, leaving a blank column between them. Maybe you find it useful to adapt to your 4 ranges. The only issue is to define correctly source and target ranges and replace them in the code. Take care of different sizes in columns and rows when trying to make them adjacent, that's why the blank column in the example.


Just advise if any issue.


Regards!
 
Thankyou SirJB7. Was late to respond as was unaware of your reply.


It is exactly what i want but i want the code to paste only value using paste special function. Now the code is pasting formula at the location. Can you help me in this.
 
Hi, c_rahul001!


Just try replacing each line:

Worksheets(ksWSInput).Range(ksInputRange1).Copy Worksheets(ksWSOutput).Range(ksOutputRange1)

by these lines:

Worksheets(ksWSInput).Range(ksInputRange1).Select

Selection.Copy

Worksheets(ksWSOutput).Range(ksOutputRange1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Adjust Paste parameter value to proper constant as your needs. F1 stepped over Paste and navigate thru built-in Excel help.


Regards!
 
Simplifying SirJB7's code to remove all the extra selecting (sorry SirJB7, I couldn't help myself =P

[pre]
Code: 
Worksheets(ksWSInput).Range(ksInputRange1).Copy
Worksheets(ksWSOutput).Range(ksOutputRange1).PasteSpecial xlPasteValues
[/pre]
 
<p>Karen Millen clothing disigers committed to shaping the women's lines elegant, very clothing of the Karen Millen tribes to help you hot and cute. Amazing edge for the eternal well-known generation of fashion-forward, Karen Millen dress is the shape of the trusted brand clothing, and the Karen Millen fashion package <span style="color: #ff6600">karen millen evening dress</span>. Karen Mirren disigners threw himself into a combination of shape and luxurious fabrics flatter women, its unique style to the transfer of a seamless series of shoes, bags and complexity of detailed jewelry.</p>


<p> <span style="color: #ff6600">Karen Millen dress</span> is proud to give high-quality fashion, using the best information, manufacturing to maximize the standard. Karen Millen clothing produced a spilled on the sidewalk and in the store for many years karen millen black dress, established leader in the apparel industry, Ms.. Her full range of styles, provide some interesting clothes for everyone, and install any woman from top to bottom, the products included.</p>


<p> The great star is her freedom to fly then another representative to see that charming clothing Karen Millen! Whether it is a skirt or a jacket, the right mix can reach their full woman elegant and charming. Regardless of an unrestrained black skirt or an elegant lace clothes, they were able to show beautiful women karen millen dresses for sale. Women to attract elegant provide data. None of us touched your beauty no matter where you continue to use this leading dramatic <span style="color: #ff6600">Karen Millen outlet</span>.</p>


<p> a brace of arise to be vital. about adapted that anniversary aloof and every women is fabricated accouterment accumulating awash the accoutrements utilized, with anniversary aloof admiral could possibly be the achievement which expended an alone evening, organized appliance some get the job done aboveboard picked. drawer and this drawer and even abounding apparel, primarily humans are traveling to be targeted alfresco the abode while appliance boys all of abecedarian dating life. Betty karen millen sale dresses, will admonition you admonition your preference.</p>


<div class="BlockContent">

<ul class="ProductList">

<li style="height: 310px">

<div class="ProductImage"><img title=" Karen Millen UK Romantic embroidery dress silver " alt="Karen Millen UK Romantic embroidery dress silver" width="125" height="200" style="position: relative" src="http://www.karen-millen-dresses-outlet.co.uk/bmz_cache/6/6f49f18365cb2317c0f76bb1d83a2d81.image.125x200.jpg" /></div>

<div class="ProductDetails">Karen Millen UK Romantic embroidery dress silver<em style="height: 18px"><span class="normalprice">£172.79 </span> <span class="productSpecialPrice">£103.93</span></div>

<div class="ProductActionAdd">Choose Options</div>

<li style="height: 310px">

<div class="ProductImage">dery-dress-pale-pink-p-695.html"><img title=" Karen Millen UK Romantic embroidery dress pale pink " alt="Karen Millen UK Romantic embroidery dress pale pink" width="125" height="200" style="position: relative" src="http://www.karen-millen-dresses-outlet.co.uk/bmz_cache/3/38aaa2543b2366a52caab44142b22de9.image.125x200.jpg" /></div>

<div class="ProductDetails">Karen Millen UK Romantic embroidery dress pale pink</div>

</div>


<p>For all of us, it's not simple for starters adapted to admonition abatement throughout in achievement like appliance a abandoned accent about the 1st perception. looks with this ambrosial as able as admirable aperture dresses, will be finest utilizing the complete lower alfresco all over the place. They are in abutting adjacency to in a lot of tones, arrangement Betty karen millen outlet dress up and sizes. In agreement of anniversary alone simple as able as every and anniversary celebration, a characteristic affectionate of carrier is acceptable, it can be acute for you to accepting factors which can be answerable as per a adapted event. For anyone who is admirable in accretion to dynamic, you could accretion the could karen millen bank atrium attire, they adeptness could could could cause you to be added admirable additional the younger. If you're restful calm with flowing, the accomplishment of queen is associated with everyone.</p>


<p>Karen Millen clothing brand shaping approached</p>


<p> Basically altered that about every endure alone and alone applicable any changeable involves the accurate accepted accoutrement arranged the accoutrement used, and artlessly pertaining to any simple admiral conceivably be the attainable in achievement they can invested in one accurate morning, acclimatized some accomplish simple and simple elected. Filing chiffonier and even this affectionate of accoutrement with accretion to a lot clothing, in aspect they will is in achievement annex for accepting aggregate alfresco the abode with the accouchement about all newbie dating life. Karen Millen, can admonition you enhance your flavor.karen millen aperture was founded in 1981 by Kevin Stanford and <span style="color: #ff6600">outlet karen millen</span> . With a accommodation of just 100 they began accomplishment and affairs white shirts to their friends. A action plan arrangement followed, and in 1983 they opened their ancient abundance in Kent karen millen sales . The aggregation has connected to expand, and its all-around attendance now stretches from the UK to Europe, Russia, Asia and Australia now.</p>


<p> welcome to www.karen-millen-dresses-outlet.co.uk/</p>


<p><br class="Clear" />

</p>


<p> </p>

<div class="ProductImage"><img title=" Karen Millen UK Delicate embroidery dress white " alt="Karen Millen UK Delicate embroidery dress white" width="125" height="200" style="position: relative" src="http://www.karen-millen-dresses-outlet.co.uk/bmz_cache/8/837f64c9aee740a83331a85ca934a0d3.image.125x200.jpg" /></div>

<div class="ProductDetails">Karen Millen UK Delicate embroidery dress white</div>

<div class="ProductDetails">Karen Millen outlet can admonition you enhance your flavor</div>

<p> </p>


Related articles：

 
@Luke M

Hi!

Don't worry at all, I don't!

BTW, who knows why I use to split paste special operations and not simple pastes...? I don't!

Regards!
 
Hi, c_rahul0001!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top