excel - How to copy and paste values into next empty row in table on the same Worksheet
问题描述
I have 2 forms on the same worksheet. The first allows users to input contracted hours / start dates to determine their annual leave entitlement. This is a formula and the figures are in cells I20, J20 and K20.
When the User clicks a command button, these figures are to be copied over to a Running Total form on the same worksheet, to cells P17, Q17 and R17. If the User then changes their contracted hours on the first form and clicks the command button again, then the new figures are to be copied over to cells P18, Q18 and R18. This step can be repeated a number of times (the last row for copying the data is P22, Q22, R22).
This Running Total form has on its very last row a formula that sums the totals that have been put on the form.
I'm new to VBA but have managed to create the following code that copies the figures into P17, Q17 and R17 - but I'm not sure how to get the next figures into the row below. I feel this should be a straight forward thing to do but I am struggling a little with this. Any help would be appreciated.
Sub CopyRange()
'Value Paste Cells
Worksheets("Calculator").Activate
Range("P15").End(xlDown).Offset(1, 0).Select
ActiveCell.Value = Range("I20")
ActiveCell.Offset(0, 1).Value = Range("J20")
ActiveCell.Offset(0, 2).Value = Range("K20")
Range("P23").End(xlUp).Offset(1, 0).Select
End Sub
Initially I get the figures showing in the Running Total Form copied into P17, Q17 and R17 after clicking the command button. It then shows active cell to be P18. If I then change the figures on the first form and click the command button again - nothing happens.
解决方案
如果没有看到您的工作表,很难确切地知道在哪里放置 andEnd(xlUp)
或End(xlDown)
,但这应该可以让您以一种好的方式开始。根据需要进行调整。
With Worksheets("Calculator")
.Range("P23").End(xlUp).Offset(1, 0).Resize(1,3).value = .Range("I20:K20").Value
End With
推荐阅读
- android - 计费库 - 测试不同国家/地区的 InApp 订阅
- laravel - Laravel 关系搜索
- r - 如何选择比较具有相同值的多列的行?
- html - 如何创建一个可变高度表,其中每个单元格都包含一个输入字段?
- php - 在寡妇和 php 8 上调用未定义的函数 pspell_new()
- javascript - 允许在 href 中使用自定义协议
- git - Bring Git branch to state of commit in other branch
- sql - Postgres jsonb_build_object 为空行返回空而不是空
- swift - 创建自定义框架和 xcframework 时出错
- python - 多元回归,用多个自变量重塑输入