我有一列数据,我想将其中四个单元格的区域复制到不同工作表中的另一个区域。这应该是一个循环,每次都会更改要复制的起始单元格。
我或多或少是宏编程的新手,无法超越以下代码,这在一开始就给了我一个编译错误("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