Excel VBA粘贴到多个范围

问题描述：

我试图将公式粘贴到多个范围中。实际上我有很多我想粘贴的范围。有没有办法循环这个列表，而不必为每个范围编写代码？我试图粘贴范围列表成一行是太长一行Excel VBA粘贴到多个范围

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

不知道如何做到这一点。我是初学者。 TIA

你能改说你的问题吗？我已经读了两遍，它依然在我头顶上徘徊。 – Hambone

无论你想做什么，哪一个都不清楚，那不是做到这一点的方法。你有没有尝试写公式，然后填写？ – CustodianOfCode

答

我希望我的目标是正确的 - 复制源范围并粘贴到同一工作簿中的多个范围。

你可以用一个循环，并Split()做到这一点，可以考虑如下：

A1:G1具有10到70
A2固定值具有式=A1+1
A3具有式=A1*2

以下代码将范围A2:A3复制到多个范围，用逗号分隔文本字符串"B2:B3,C2:C3,D2:D3,E2:E3,F2:F3,G2:G3"。您可以放置全范围文本，如'Sheet2'!B2:B3,...。

代码粘贴范围：

Option Explicit 

Sub PasteRanges() 
    Dim oRngSrc As Range, oItem As Variant, sPasteTo As String 

    Set oRngSrc = Range("A2:A3") ' Source Range to Copy From 
    sPasteTo = "B2:B3,C2:C3,D2:D3,E2:E3,F2:F3,G2:G3" 
    ' This works: 
    oRngSrc.Copy Range(sPasteTo) 
    ' If it complains about being too long, use below loop 
    For Each oItem In Split(sPasteTo, ",") 
     oRngSrc.Copy Range(oItem) ' Paste to oItem 
    Next 
End Sub

结果：

我个人不推荐这种方法，但是这是你的要求。

Excel VBA粘贴到多个范围

相关推荐