vba - 从具有相同目标的不同工作表中复制粘贴

Question

我必须执行相同的复制粘贴，但对于不同工作表（4、6、8 等）中的更多列，不像 j...

这些行正在使用 arrRows（感谢 Tinman）

• 但是对于列，我尝试了 3 个 fors（一个用于工作表，一个用于列，一个用于行）不起作用，因为它没有跳转到下一个 j（在经过所有 n 之后只是转到下一列）不是下一张（这是我需要的）

• 我尝试了另一条路径（当前代码）我无法找到将 j 作为变量的序列可以处理 arrcolumns 中的正确数字..也许我可以做一个 do..loop？

子 GHM()

Dim j As Integer
Dim n As Long, r, c As Long
Dim arrRows, arrColumns As Variant
Dim Source As Range, Target As Range



For j = 4 To 18 Step 2




Set Source = Workbooks("180610_book1.xlsm").Worksheets(j).Cells
Set Target = Workbooks("180610_book2.xlsm").Worksheets("RAW DATA").Cells





arrRows = Array(5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 127, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194)
arrColumns = Array(9, 14, 19, 24, 29, 34, 39, 44)


c = arrColumns(j - 4)

For n = 2 To 35
     r = arrRows(n - 2)
    Target.Cells(r, c).Resize(1, 5).Value = WorksheetFunction.Transpose(Source.Cells(4, n).Resize(5, 1).Value)
Next

下一个

结束子

标签： vbafor-loopcopypastenext

Answer 1

我添加了一个直到只有列的计数器并且它有效

    Dim j, x As Integer
    Dim n As Long, r, c As Long
    Dim arrRows, arrColumns As Variant
    Dim Source As Range, Target As Range

    x = 0
    For j = 4 To 18 Step 2

        Set Source = Workbooks("180610_SequencingScenarioTEST1.xlsm").Worksheets(j).Cells
        Set Target = Workbooks("180610_SequencingScenarioTEST1.xlsm").Worksheets("RAW DATA").Cells
        arrRows = Array(5, 10, 15, 23, 28, 33, 38, 43, 48, 53, 61, 66, 71, 79, 84, 89, 94, 102, 107, 112, 117, 122, 127, 135, 140, 148, 153, 158, 166, 171, 179, 184, 189, 194)
        arrColumns = Array(9, 14, 19, 24, 29, 34, 39, 44)

        Do
            c = arrColumns(x)
            For n = 2 To 35

                r = arrRows(n - 2)
                Target.Cells(r, c).Resize(1, 5).Value = WorksheetFunction.Transpose(Source.Cells(4, n).Resize(5, 1).Value)
            Next                                      'for each column being transpose to row in target sheet

            x = x + 1

        Loop Until x <> x + 1                         'for each columns in raw data = the different scenarios
    Next                                              'for j=new sheet
End Sub