更新时间:2023-01-30 19:59:20
有意粘贴这些值,那么直接的值传递就更有效率,而且不涉及剪贴板/ p>
with Sheets(IND BASKET)。范围(A2:I76)
/ pre>
表格(IND TOTAL ).Range(A& Rows.Count).End(xlUp).Offset(1,0).Resize(.Rows.Count,.Columns.Count)= .Value
end with使用您自己的副本,粘贴特殊xlPasteValues,您只需要指定目标左上角的单元格,并允许复制的区域定义大小和形状。
表格(IND BASKET)。范围(A2:I76)。复制
表格(IND TOTAL)。Range(A& Rows.Count).End(xlUp).Offset(1,0).PasteSpecial xlPasteValues您的命名范围的定义可能更好,没有易失性的OFFSET功能,如
'或仅为第一个单元格
= INDEX('IND TOTAL'!$ A:$ A,MATCH (zzz,IND TOTAL!$ A:$ A)+1)该列A包含我使用COUNTA和...推断的文本不COUNT。如果列A包含数字,请将1e99交换为zzz。
I'm having a few issues working this out as I'm new to VBA but I'm sure it has a pretty simple solution.
I'm essentially wanting to automate the addition of new data to a sheet.
Sheet: INB BASKET Cells: A2:I76 contains live links to another worksheet. Basically I want to copy these as Values to Sheet IND TOTAL below the latest entry on a button press (not continually updating).
I've created a dynamic named range (PasteRange) in excel that selects the 75 rows below the last entry that I want to paste into:
=OFFSET('IND TOTAL'!$A$1,COUNTA('IND TOTAL'!$A:$A),0,75,9)
I've then created a module with the following:
Sub CopyRange() Dim CopyFrom As Range Set CopyFrom = Sheets("IND BASKET").Range("A2", [I76]) et PasteArea = Sheets("IND TOTAL").Range("PasteRange") CopyFrom.Copy PasteArea.PasteSpecial xlPasteValues End Sub
But to no success as of yet, please advise.
If you are only interested in pasting the values, then a direct value transfer is more efficient and does not involve the clipboard.
with Sheets("IND BASKET").Range("A2:I76") Sheets("IND TOTAL").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Resize(.Rows.Count, .Columns.Count) = .Value end with
With your own Copy, Paste Special xlPasteValues you only need to specify the cell in the top-left corner of the destination and allow the copied area to define the size and shape.
Sheets("IND BASKET").Range("A2:I76").Copy Sheets("IND TOTAL").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
The definition of your named range might be better without the volatile OFFSET function like,
=INDEX('IND TOTAL'!$A:$A, MATCH("zzz",'IND TOTAL'!$A:$A )+1):INDEX('IND TOTAL'!$I:$I, MATCH("zzz",'IND TOTAL'!$A:$A ) + 75) 'or for just the first cell =INDEX('IND TOTAL'!$A:$A, MATCH("zzz",'IND TOTAL'!$A:$A )+1)
That assumes that column A contains text which I deduced by your use of COUNTA and not COUNT. If column A contains numbers, swap 1e99 for "zzz".