用于从一个变量范围复制到另一个变量区域的宏



我有一列数据,我想将其中四个单元格的区域复制到不同工作表中的另一个区域。这应该是一个循环,每次都会更改要复制的起始单元格。

我或多或少是宏编程的新手,无法超越以下代码,这在一开始就给了我一个编译错误("Sub或函数未定义"),我不确定原因是什么。。。此外,我有一种感觉,我在改变我的变量,但实际上并没有把一些东西打印到单元格中。

如有任何帮助,我们将不胜感激!

干杯,达莱

Sub CopyingPeriod2()
    Dim ws As Worksheet
    Set ws = Worksheets("1")
    Dim OneCells As Range
    Set OneCells = ws.Range("C1:C4")
    Dim works As Worksheet
    Set works = Worksheet("New")
    Dim NewCells As Range
    Set NewCells = works.Range("J51:J54")
    Do Until ActiveCell.Value = ""
        NewCells.Value = OneCells.Value
        OneCells = OneCells.Offset(8, 0)
        NewCells = NewCells.Offset(0, 1)

    Loop
End Sub

更新:

回答评论:每次循环运行时,C1:C4范围将偏移8行。J51单元格将在每次循环运行时偏移1列。

我继续尝试,得到了以下代码(还没有包含循环):复制粘贴作品。但是,当更新OneCells时,工作表中原始区域的单元格变为空白,OneCells.Select选择与之前相同的区域

Sub CopyingPeriod2d()
    Dim OneCells As Range
    Set OneCells = Range("C1:C4")
    Worksheets("1").Activate
    OneCells.Select
    Selection.Copy
    Worksheets("New").Activate
    Range("J51").PasteSpecial xlPasteValues
    Worksheets("1").Activate
    OneCells.Select
    ActiveCell.Offset(8, 0).Select
    OneCells = Range(ActiveCell, ActiveCell.Offset(4, 0))
    OneCells.Select

最终,它开始工作=)

Sub CopyingPeriod2d()
    Dim worksh As Worksheet
    Dim ws As Worksheet
    Dim OneCells As Range
    Dim NewCells As Range
    Set worksh = Sheets("1")
    Set ws = Sheets("New")

'AcCoa data copy
    Set OneCells = worksh.Range("C1:C4")
    Set NewCells = ws.Range("J51")
    i = 1

    'Selection of first cells, else the loop would not run properly since it is checking for the ActiveCell content
    Sheets("1").Activate
    OneCells.Select

    Do Until ActiveCell.Value = ""
        OneCells.Copy
        'Paste to destination and update destination for next iteration of the loop
        NewCells.PasteSpecial xlPasteValues
            If i = 6 Or i = 12 Then
                Set NewCells = NewCells.Offset(0, 3)
            Else
                Set NewCells = NewCells.Offset(0, 1)
            End If
            i = i + 1

        Set OneCells = OneCells.Offset(12, 0)
        OneCells.Select
    Loop

最新更新