VBA副本&粘贴动态范围 [英] VBA copy & paste with dynamic range

问题描述

我是VBA的新人，我被困在某个地方。我必须将列A的最后一行复制到列H，并粘贴到第一列的最后一行。列的最后一行将始终更改。

例如;我的数据是A2：H2，I5是最后一个有数据的单元格。

我的代码应该是复制A2：H2并粘贴它A3：H5。第二次运行宏（在将相应的列添加新数据之后）应该是复制A6：H6，并粘贴到列I的最后一行。

$ b $我写了两个不符合我需求的代码。

第一个代码是;

  Sub OrderList1（）
 
范围（a65536）。End（xlUp） 1，8）.Copy _ 
（Cells（Cells（Rows.Count，9）.End（xlUp）.Row，1））
 
 End Sub 
  

此代码跳过A3：H4，只有粘贴到A5：H5

第二个代码是;

  Sub OrderList2（）
范围（A2：H2 ）.Copy范围（单元格（2,8），_ 
单元格（单元格（Rows.Count，9）.End（xlUp）.Row，1））
 
 End Sub 
  

它复制A2：H3并粘贴A5：H5，但是当我添加新数据时，它不会开始从A5：H5粘贴。它从A2：H2开始，并覆盖到旧数据。
我可以看到我要改变什么，范围应该是动态范围，如第一个代码，但是我无法设法编写代码。

我真的很乐意帮助。

解决方案

想要使用它作为起点：

  Dim columnI As Range 
 Set columnI = Range（I：I ）
 
 Dim columnA As Range 
设置columnA =范围（A：A）
 
'查找列A中哪个单元格为空的第一行
 Dim c As Range 
 Dim i As Long 
i = 1 
对于每个c在columnA.Cells 
如果c.Value2 =然后退出
i = i + 1 
下一步c 
 
'好的，我们已经找到了，现在我们可以将上一行
'的列A到H的范围引用到变量（在上一行中，列A还没有为空，所以这是我们要
'复制的行）
 Dim lastNonEmptyRow As Range 
 Set lastNonEmptyRow = Range（Cells（i  -  1， 1），Cells（i  -  1，8））
 
'，现在将此范围复制到所有其他行，只要columnI不为空
 Do While columnI（i）< > 
 lastNonEmptyRow.Copy范围（单元格（i，1），单元格（i，8））
i = i + 1 
循环
  / pre> 
I'm new with VBA and I'm stuck somewhere. I have to copy last row of column A till column H and paste it untill the last row of column I. Last rows of columnns will be always change.

e.g; my data is in A2:H2 and I5 is the last cell with data.

My code should be copy A2:H2 and paste it A3:H5. And second time I run the macro (after I add new data to respective columns) it should be copy A6:H6 and paste it untill the last row of column I.

I wrote two codes which were not fulfill my needs.

first code is; 
  Sub OrderList1()

    Range("a65536").End(xlUp).Resize(1, 8).Copy _
    (Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))

  End Sub
this code skips A3:H4 and only pastes to A5:H5 

second code is;
 Sub OrderList2()
   Range("A2:H2").Copy Range(Cells(2, 8), _
   Cells(Cells(Rows.Count, 9).End(xlUp).Row, 1))

 End Sub
it copies A2:H3 and paste it A5:H5 but when I add new data it doesn't start to paste from A5:H5. It start from A2:H2 and overwrite to old data.
I can see what I have to change,range should be dynamic range like in the first code,but I can't manage to write the code. 

I'll really appreciate little help.
解决方案
You might want to use this as a starting point:
Dim columnI As Range
Set columnI = Range("I:I")

Dim columnA As Range
Set columnA = Range("A:A")

' find first row for which cell in column A is empty
Dim c As Range
Dim i As Long
i = 1
For Each c In columnA.Cells
    If c.Value2 = "" Then Exit For
    i = i + 1
Next c

' ok, we've found it, now we can refer to range from columns A to H of the previous row
' to a variable (in the previous row, column A has not been empty, so it's the row we want
' to copy)
Dim lastNonEmptyRow As Range
Set lastNonEmptyRow = Range(Cells(i - 1, 1), Cells(i - 1, 8))

' and now copy this range to all further lines, as long as columnI is not empty
Do While columnI(i) <> ""
   lastNonEmptyRow.Copy Range(Cells(i, 1), Cells(i, 8))
   i = i + 1
Loop


                        
这篇关于VBA副本&amp;粘贴动态范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！