在VBA Excel中复制和粘贴循环以获得多个输出 [英] Copying and pasting loop in VBA Excel for multiple outputs

问题描述

所以我有以下excel选项卡：
代码1，代码2，代码3，LI，2015、2016、2017、2018、2019、2015年的输出等。

So I have the following excel tabs: Code 1, Code 2, Code 3, LI, 2015, 2016, 2017, 2018, 2019, output for 2015 etc.

对于2015年，我在 2015标签中有一张表格，其中有10行，列出了3个代码及其各自的％值。例如

For 2015, I have a table in '2015' tab with 10 rows a list of 3 code and their respective % values. e.g.

ref       name   yr     code 1   %     code 2   %      code 3    %
12345   NAME    2015    AB  50% CD  37% EF  13%
78901   NAME    2015    AX  54% OD  30% NG  6%
26572   NAME    2015    AE  60% CD  27% PF  13%

我需要将代码1'AB'和％'50％'放入选项卡Code 1中的单元格B5和B6中。对于代码2和3'CS'相同标签代码2等中的B5和B6中的 37％和 37％。然后，它们在F5：F183单元格的LI选项卡中生成一个模式，然后需要复制每个参考并粘贴到每个参考的2015选项卡的输出中。

I need the code 1 'AB' and % '50%' to be put into cells B5 and B6 in the tab Code 1. Same for codes 2 and 3 'CS' and '37%' in B5 and B6 in tab Code 2 etc. These then produce an pattern in the LI tab in cells F5:F183 which then needs copying for each reference and pasting into the output for 2015 tab for each reference. Then this loops for each reference and repeats pasting the output.

到目前为止，对于复制和粘贴部分，我已经有了它：

So far I have this for the copying and pasting part:

    Sub Copy_and_paste2()
        Dim rng2 As Range, cell2 As Range
        Dim i As Integer
        i = 3
        Set rng2 = Worksheets("2015").Range("D10:D21")
        For Each cell2 In rng2
           Worksheets("Code 1").Range("B5").Value = cell2.Value  
            Worksheets("2015 output").Range("A" & i & ":AW" & i).Value = Worksheets("LI").Range("F5:F183").Value
            i = i + 1
        Next cell2
End Sub

我只是试图使其在没有％的第一个代码中起作用，然后可以尝试添加后面的代码，但是此代码也不起作用。有什么建议吗？

At this point I was just trying to make it work for the first code with no % then I can try add the later ones but this one doesn't work either. Any advice?

推荐答案

您的问题有点难以理解，因此我根据自己的想法写了一个答案您的代码最明显的问题。抱歉，如果我误读了您的查询或完全错过了要点。

Your question is a little hard to read so I've written an answer based on what I think is the most obvious issue with your code. Apologies if I've misread your query or missed the point entirely..

您似乎正在尝试复制

Worksheets（ LI）。Range（ F5：F183）（179个单元格）

至

Worksheets（ 2015 output）。Range（ A& i&：AW& i） （49个单元格）

忽略了您无法将179个条目复制到49（它只会复制前49个）的事实-认为您可能会看到所有49个单元格都重复了第一个条目？

Ignoring the fact that you can't copy 179 entries into 49 (it will only copy the first 49) - I think you are probably seeing the first entry repeated across all 49 cells?

这是因为您没有转换范围（在这种情况下，从列切换为

This would be because you're not transposing the range (in this case, switching from a column to a row).

尝试类似的操作进行转置：

Try something like this to Transpose:

Worksheets("2015 output").Range("A" & i & ":AW" & i).Value = _
                WorksheetFunction.Transpose(Worksheets("LI").Range("F5:F57").Value)

不过请记住，我将源范围更改为科幻第49个单元格。您不必这样做，但如果您不知道这种情况正在发生，我会着重强调。

Keep in mind though, I've changed the source range to just the first 49 cells. You don't have to but I'm highlighting it in case you weren't aware this is happening.

这篇关于在VBA Excel中复制和粘贴循环以获得多个输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！