这是我下面的代码,它只是不确定为什么当它复制到第二列和第三列时它会向下移动一行。
Dim shB As Worksheet, shPM As Worksheet, lastRowB As Long, lastRowPM As Long
Dim shC As Worksheet, shD As Worksheet
Dim i As Long, lastCol As Long
Dim eRow As Long
Set shB = Worksheets("Billable")
Set shPM = Worksheets("PM_Forecast")
lastRowB = Worksheets("Billable").Cells(Rows.Count, 1).End(xlUp).Row
''Loop will run from row 6 to the last row (Row 6 is the first row in table)
For i = 6 To lastRowB
''Check Billable requests first
If shB.Cells(i, 15).Value = "Detailed Estimate Submitted" Then
''Copy over ID reference
shB.Cells(i, 2).Copy
eRow = shPM.Cells(Rows.Count, 1).End(xlUp).Row
shB.Paste Destination:=shPM.Cells(eRow + 1, 1)
''Copy over title
shB.Cells(i, 3).Copy
eRow = shPM.Cells(Rows.Count, 1).End(xlUp).Row
shB.Paste Destination:=shPM.Cells(eRow + 1, 2)
''Copy over Effort
shB.Cells(i, 9).Copy
eRow = shPM.Cells(Rows.Count, 1).End(xlUp).Row
shB.Paste Destination:=shPM.Cells(eRow + 1, 3)
下一个结束
这是结果的图片,也许有人可以告诉我我哪里出错了。
eRow
尝试粘贴到下一列时,不要每次都计算(基于 A:A 列)。
每次迭代都使用shB.Paste Destination:=shPM.Cells(eRow , 2)
(not eRow + 1
)。
否则,A:A 列中的新增值将添加另一行到eRow
...
或者计算每列的最后一行:
eRow = shPM.Cells(Rows.Count, 2).End(xlUp).Row
和eRow = shPM.Cells(Rows.Count, 3).End(xlUp).Row
,根据您打算复制值的列。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句