Excel VBA pasting to multiple ranges -
i trying paste formula multiple ranges. have long list of ranges want paste to. there way loop through list without having write code each range? tried pasting list of ranges 1 line it's long 1 line
range('lcm-00 (2)'!$j$57:$y$57).copy range('lcm-00 (2)'!$j$57:$y$57,'lcm-00 (2)'!$j$54:$y$54,'lcm-00 (2)'!$k$50:$y$50,'lcm-00 (2)'!$k$47:$y$47,'lcm-00 (2)'!$ab$57:$aq$57,'lcm-00 (2)'!$ab$54:$aq$54,'lcm-00 (2)'!$ac$50:$aq$50,'lcm-00 (2)'!$ac$47:$aq$47,'lcm-00 (2)'!$at$57:$bi$57,'lcm-00 (2)'!$at$54:$bi$54,'lcm-00 (2)'!$au$50:$bi$50,'lcm-00 (2)'!$au$47:$bi$47,'lcm-00 (2)'!$bl$57:$ca$57,'lcm-00 (2)'!$bl$54:$ca$54,'lcm-00 (2)'!$bm$50:$ca$50,'lcm-00 (2)'!$bm$47:$ca$47,'lcm-00 (2)'!$cd$57:$cs$57,'lcm-00 (2)'!$cd$54:$cs$54,'lcm-00 (2)'!$ce$50:$cs$51,'lcm-00 (2)'!$ce$47:$cs$47,'lcm-00 (2)'!$j$42:$y$42,'lcm-00 (2)'!$k$35:$y$35,'lcm-00 (2)'!$k$32:$y$32,'lcm-00 (2)'!$ab$42:$aq$42,'lcm-00 (2)'!$ab$39:$aq$39,'lcm-00 (2)'!$ac$35:$aq$35,'lcm-00 (2)'!$ac$32:$aq$32,'lcm-00 (2)'!$at$42:$bi$42,'lcm-00 (2)'!$at$39:$bi$39,'lcm-00 (2)'!$au$35:$bi$35,'lcm-00 (2)'!$au$32:$bi$32,'lcm-00 (2)'!$bl$42:$ca$42,'lcm-00 (2)'!$bl$39:$ca$39,'lcm-00 (2)'!$bm$35:$ca$35,'lcm-00 (2)'!$bm$32:$ca$320,'lcm-00 (2)'!$cd$42:$cs$42,'lcm-00 (2)'!$cd$39:$cs$39,'lcm-00 (2)'!$ce$35:$cs$35,'lcm-00 (2)'!$ce$32:$cs$32,'lcm-00 (2)'!$j$28:$y$28,'lcm-00 (2)'!$j$25:$y$25,'lcm-00 (2)'!$k$21:$y$21,'lcm-00 (2)'!$ab$28:$aq$28,'lcm-00 (2)'!$ab$25:$aq$25,'lcm-00 (2)'!$ac$21:$aq$21,'lcm-00 (2)'!$ac$18:$aq$18,'lcm-00 (2)'!$at$28:$bi$28,'lcm-00 (2)'!$at$25:$bi$25,'lcm-00 (2)'!$au$21:$bi$21,'lcm-00 (2)'!$au$18:$bi$18,'lcm-00 (2)'!$bl$28:$bi$28,'lcm-00 (2)'!$bl$25:$bi$25,'lcm-00 (2)'!$bm$21:$ca$21,'lcm-00 (2)'!$bm$18:$ca$18,'lcm-00 (2)'!$cd$28:$cs$28,'lcm-00 (2)'!$cd$25:$cs$25,'lcm-00 (2)'!$ce$21:$cs$21,'lcm-00 (2)'!$ce$18:$cs$18,'lcm-00 (2)'!$j$14:$y$14,'lcm-00 (2)'!$j$11:$y$11,'lcm-00 (2)'!$k$7:$y$7,'lcm-00 (2)'!$k$4:$y$4,'lcm-00 (2)'!$ab$14:$aq$14,'lcm-00 (2)'!$ab$11:$aq$11,'lcm-00 (2)'!$ac$7:$aq$7,'lcm-00 (2)'!$ac$4:$aq$4,'lcm-00 (2)'!$at$14:$bi$14,'lcm-00 (2)'!$at$11:$bi$11,'lcm-00 (2)'!$au$7:$bi$7,'lcm-00 (2)'!$au$4:$bi$4,'lcm-00 (2)'!$bl$14:$ca$14,'lcm-00 (2)'!$bl$11:$ca$11,'lcm-00 (2)'!$bm$7:$ca$7,'lcm-00 (2)'!$bm$4:$ca$4,'lcm-00 (2)'!$cd$14:cs$14,'lcm-00 (2)'!$cd$11:cs$11,'lcm-00 (2)'!$ce$7:cs$7,'lcm-00 (2)'!$ce$4:cs$44).select range.xlpaste not sure how this. i'm beginner. tia
i hope have got goal right - copy source range , paste multiple ranges across same workbook.
you can loop , split(), consider below:
a1:g1has fixed value1070a2has formula=a1+1a3has formula=a1*2
the code below copy range a2:a3 multiple ranges, separated comma in text string "b2:b3,c2:c3,d2:d3,e2:e3,f2:f3,g2:g3". can put full range text 'sheet2'!b2:b3,....
code paste ranges:
option explicit sub pasteranges() dim orngsrc range, oitem variant, spasteto string set orngsrc = range("a2:a3") ' source range copy spasteto = "b2:b3,c2:c3,d2:d3,e2:e3,f2:f3,g2:g3" ' works: orngsrc.copy range(spasteto) ' if complains being long, use below loop each oitem in split(spasteto, ",") orngsrc.copy range(oitem) ' paste oitem next end sub result:
don't recommend approach, asked.
Comments
Post a Comment