{"id":6065,"date":"2015-07-05T12:03:04","date_gmt":"2015-07-05T10:03:04","guid":{"rendered":"http:\/\/www.auditsi.eu\/?p=6065"},"modified":"2015-07-05T12:03:04","modified_gmt":"2015-07-05T10:03:04","slug":"excel-et-vba-le-copier-coller-a-toutes-les-sauces","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=6065","title":{"rendered":"Excel et VBA : le copier \/ coller \u00e0 toutes les sauces"},"content":{"rendered":"<p style=\"text-align: justify;\">L\u2019av\u00e8nement des <a href=\"http:\/\/www.auditsi.eu\/?page_id=2493\">syst\u00e8mes d\u2019exploitation multit\u00e2ches<\/a> au milieu des ann\u00e9es 80 s\u2019est accompagn\u00e9 de nombreuses innovations techniques. Le copier \/ coller \u00e0 la souris au sein d\u2019un m\u00eame logiciel ou entre diff\u00e9rents logiciels est sans aucun doute une v\u00e9ritable r\u00e9volution qui encore aujourd\u2019hui est utilis\u00e9 au quotidien.<\/p>\n<p style=\"text-align: justify;\">La fonction copier \/ coller du tableur Excel\u00a0 n\u2019\u00e9chappe pas \u00e0 la r\u00e8gle et propose une multitude d\u2019options, pour certaines m\u00e9connues de la plupart des utilisateurs malgr\u00e9 leur grande utilit\u00e9.<\/p>\n<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Fen\u00eatre-Collage-Sp\u00e9cial.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6068\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Fen\u00eatre-Collage-Sp\u00e9cial.png\" alt=\"Fen\u00eatre Collage Sp\u00e9cial (EXCEL)\" width=\"574\" height=\"336\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Fen\u00eatre-Collage-Sp\u00e9cial.png 574w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Fen\u00eatre-Collage-Sp\u00e9cial-300x176.png 300w\" sizes=\"auto, (max-width: 574px) 100vw, 574px\" \/><\/a><\/p>\n<p style=\"text-align: justify;\">Principales cat\u00e9gories d\u2019options \u00e0 disposition de l\u2019utilisateur\u00a0:<\/p>\n<ul>\n<li style=\"text-align: justify;\">Options de collage\u00a0:\n<ul>\n<li style=\"text-align: justify;\">Tout\u00a0: copie l\u2019ensemble de la plage de cellule et la colle \u00e0 l\u2019identique (formules et format) sur une plage de destination<\/li>\n<li style=\"text-align: justify;\">Formules\u00a0: ne recopie que les formules (\u00e0 l\u2019exclusion des formats)<\/li>\n<li style=\"text-align: justify;\">Valeurs\u00a0: seuls le r\u00e9sultat des formules est recopi\u00e9 (les formules de calculs ne sont donc pas recopi\u00e9es dans les cellules de destination)<\/li>\n<li style=\"text-align: justify;\">Formats\u00a0: ne recopie que les formats<\/li>\n<\/ul>\n<\/li>\n<li style=\"text-align: justify;\">Op\u00e9ration\u00a0: ajouter, soustraire\u2026 les donn\u00e9es copi\u00e9es avec les donn\u00e9es contenues dans la plage de destination.<\/li>\n<li style=\"text-align: justify;\">Transpos\u00e9\u00a0: permet de d\u2019\u00e9changer la disposition d\u2019origine (colonnes\/lignes) dans la plage de destination (lignes\/colonnes) :<\/li>\n<\/ul>\n<div id=\"attachment_6077\" style=\"width: 517px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-avant-collage-transpos\u00e9.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6077\" class=\"size-full wp-image-6077\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-avant-collage-transpos\u00e9.png\" alt=\"Donn\u00e9es \u00e0 copier...\" width=\"507\" height=\"121\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-avant-collage-transpos\u00e9.png 507w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-avant-collage-transpos\u00e9-300x72.png 300w\" sizes=\"auto, (max-width: 507px) 100vw, 507px\" \/><\/a><p id=\"caption-attachment-6077\" class=\"wp-caption-text\">Donn\u00e9es \u00e0 copier&#8230;<\/p><\/div>\n<div id=\"attachment_6078\" style=\"width: 411px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-apr\u00e8s-collage-transpos\u00e9.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6078\" class=\"size-full wp-image-6078\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-apr\u00e8s-collage-transpos\u00e9.png\" alt=\"Les m\u00eames donn\u00e9es apr\u00e8s un collage transpos\u00e9\" width=\"401\" height=\"122\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-apr\u00e8s-collage-transpos\u00e9.png 401w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Excel-Tableau-apr\u00e8s-collage-transpos\u00e9-300x91.png 300w\" sizes=\"auto, (max-width: 401px) 100vw, 401px\" \/><\/a><p id=\"caption-attachment-6078\" class=\"wp-caption-text\">Les m\u00eames donn\u00e9es apr\u00e8s un collage transpos\u00e9<\/p><\/div>\n<ul>\n<li style=\"text-align: justify;\">Coller avec liaison\u00a0: les donn\u00e9es coll\u00e9es dans la plage de destination sont automatiquement mises \u00e0 jour en cas de modification ult\u00e9rieure des donn\u00e9es source.<\/li>\n<\/ul>\n<div id=\"attachment_6080\" style=\"width: 165px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Acc\u00e8s-au-collage-sp\u00e9cial....png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-6080\" class=\"size-full wp-image-6080\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2015\/07\/Acc\u00e8s-au-collage-sp\u00e9cial....png\" alt=\"Acc\u00e8s au collage sp\u00e9cial...\" width=\"155\" height=\"298\" \/><\/a><p id=\"caption-attachment-6080\" class=\"wp-caption-text\">Acc\u00e8s au collage sp\u00e9cial&#8230;<\/p><\/div>\n<ul>\n<li style=\"text-align: justify;\">Image : les donn\u00e9es sont coll\u00e9es sous forme d&#8217;une image.<\/li>\n<li style=\"text-align: justify;\">Image li\u00e9e : les donn\u00e9es sont coll\u00e9es sous forme d&#8217;une image avec liaison (mises \u00e0 jour automatiquement en cas de modification ult\u00e9rieure des donn\u00e9es source).<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Les diff\u00e9rentes cat\u00e9gories d\u2019options sont cumulables entre elles.<\/p>\n<p style=\"text-align: justify;\">Les copier \/ coller en VBA :<\/p>\n<p style=\"text-align: justify;\">Pour commencer, l&#8217;instruction pour copier :<\/p>\n<pre class=\"lang:default decode:true\">Selection.Copy<\/pre>\n<p style=\"text-align: justify;\">Programmation des collages sp\u00e9ciaux :<\/p>\n<pre class=\"lang:default decode:true\">Range(\"A8\").Select\r\nSelection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True\r\n<\/pre>\n<ul>\n<li style=\"text-align: justify;\"><em>Paste:=<\/em>\n<ul>\n<li style=\"text-align: justify;\"><em>xlPasteAll<\/em> : Tout<\/li>\n<li style=\"text-align: justify;\"><em>xlPasteValues<\/em> : Valeurs<\/li>\n<li style=\"text-align: justify;\"><em>xlPasteFormats<\/em> : Format<\/li>\n<\/ul>\n<\/li>\n<li style=\"text-align: justify;\"><em>Operation:=<\/em>\n<ul>\n<li style=\"text-align: justify;\"><em>xlSubtract<\/em> : soustraction<\/li>\n<li style=\"text-align: justify;\"><em>xlAdd<\/em> : addition<\/li>\n<li style=\"text-align: justify;\"><em>xlMultiply<\/em> : multiplication<\/li>\n<li style=\"text-align: justify;\"><em>xlDivide<\/em> : division<\/li>\n<\/ul>\n<\/li>\n<li style=\"text-align: justify;\"><em>Transpose:=True<\/em> pour un collage transpos\u00e9<\/li>\n<li style=\"text-align: justify;\">Coller avec liaison :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true \">ActiveSheet.Paste Link:=True<\/pre>\n<ul>\n<li>Image :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true\">ActiveSheet.Pictures.Paste.Select\r\nActiveSheet.Shapes.Range(Array(\"Picture 1\")).Select<\/pre>\n<ul>\n<li>\u00a0Image li\u00e9e :<\/li>\n<\/ul>\n<pre class=\"lang:default decode:true \">ActiveSheet.Pictures.Paste(Link:=True).Select\r\nActiveSheet.Shapes.Range(Array(\"Picture 2\")).Select<\/pre>\n<p>Approfondir le sujet : <a title=\"Programmer en VBA\" href=\"http:\/\/www.auditsi.eu\/?cat=1569\">Programmer en VBA<\/a> \/ <a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"http:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20et%20VBA%20%3A%20le%20copier%20%2F%20coller%20%C3%A0%20toutes%20les%20sauces\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>L\u2019av\u00e8nement des syst\u00e8mes d\u2019exploitation multit\u00e2ches au milieu des ann\u00e9es 80 s\u2019est accompagn\u00e9 de nombreuses innovations techniques. Le copier \/ coller \u00e0 la souris au sein d\u2019un m\u00eame logiciel ou entre diff\u00e9rents logiciels est sans aucun doute une v\u00e9ritable r\u00e9volution qui encore aujourd\u2019hui est utilis\u00e9 au quotidien. La fonction copier \/ coller du tableur Excel\u00a0 n\u2019\u00e9chappe &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=6065\" class=\"more-link\">Continue reading &lsquo;Excel et VBA : le copier \/ coller \u00e0 toutes les sauces&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"Excel%20et%20VBA%20%3A%20le%20copier%20%2F%20coller%20%C3%A0%20toutes%20les%20sauces\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[48,1326,1569,215],"tags":[1598,1599,1597,1596,1633,252,953,1601,1606,1561,1600,1641,1603,1605,1604,1607,1609,1608,1602],"class_list":["post-6065","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","category-programmer-en-vba","category-vba","tag-copy","tag-pastespecial","tag-collage-special","tag-copier-coller","tag-excel","tag-format","tag-liaison","tag-operations","tag-paste","tag-plage-de-cellules","tag-tranpose","tag-vba","tag-xladd","tag-xldivide","tag-xlmultiply","tag-xlpasteall","tag-xlpasteformats","tag-xlpastevalues","tag-xlsubtract"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6065","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6065"}],"version-history":[{"count":8,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6065\/revisions"}],"predecessor-version":[{"id":6084,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/6065\/revisions\/6084"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6065"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6065"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6065"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}