vba Excel - 复制选定的单元格并粘贴到另一个工作表中的列的末尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22188457/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel - Copy selected cell and paste to the end of a column in another worksheet
提问by user1226431
I'm very new at VBA and looking to copy the selected cell's contents and paste them to the next available row in Column B of Worksheet "Draft".
我对 VBA 非常陌生，希望复制所选单元格的内容并将它们粘贴到工作表“草稿”的 B 列中的下一个可用行。
I was able to figure 2 other simple macros I needed out but could figure out how to put them together to figure this out.
我能够计算出我需要的其他 2 个简单的宏，但可以弄清楚如何将它们组合在一起来解决这个问题。
Sub CopyName()
'Copy's selected cell to cell K2 within same worksheet... which then triggers vlookups with additional info
Selection.Copy
Range("K2").Select
ActiveSheet.Paste
End Sub
Sub SelectPlayer()
'Copies name pasted in K2 , to next available row in column B of other worksheet
Worksheets("Draft").Range("B" & Rows.Count).End(xlUp).Offset(1) = Worksheets("Dashboard").Range("K2").Value
End Sub
回答by Pedrumj
You would need to actually figure out the next available row in the sheet "Draft". Use this:
您需要实际找出工作表“草稿”中的下一个可用行。用这个：
Sub CopyName()
'Copy's selected cell to cell K2 within same worksheet... which then triggers vlookups with additional info
Selection.Copy
Range("K2").Select
ActiveSheet.Paste
End Sub
Sub SelectPlayer()
'Copies name pasted in K2 , to next available row in column B of other worksheet
Dim intNextEmptyCell As Integer
'assuming data on sheet "Draft" starts from row 2 column 1
intNextEmptyCell = Get_Count(2, 1, Worksheets("Draft"), true)
Worksheets("Draft").Range("B" & Strings.Trim(Str(intNextEmptyCell+1))).End(xlUp).Offset(1) = Worksheets("Dashboard").Range("K2").Value
End Sub
Get_Count is a function that I've written that gets the number of data of a column or row. Using that function you can get the number of rows of data in column "B" in the draft sheet and copy whatever is in "K2" to the next available cell. You can get more information about that function in my blog, Get Column and Row Data Count
Get_Count 是我编写的一个函数，用于获取列或行的数据数。使用该函数，您可以获得草稿表中“B”列中的数据行数，并将“K2”中的任何内容复制到下一个可用单元格。您可以在我的博客Get Column and Row Data Count 中获取有关该函数的更多信息
'intRow: The row your data starts
'intColumn: The column your data starts
'wrkSheet: The worksheet object
'flagCountRows: True if trying to get the number of rows
Public Function Get_Count(ByVal intRow As Integer, ByVal intColumn As Integer, ByRef wrkSheet As Worksheet, _
ByVal flagCountRows As Boolean) As Integer
Dim i As Integer
Dim flag As Boolean
i = 1
flag = True
While flag = True
If flagCountRows = True Then
If wrkSheet.Cells(i + intRow - 1, intColumn) <> "" Then
i = i + 1
Else
flag = False
End If
Else
If wrkSheet.Cells(intRow, intColumn + i–1) <> "" Then
i = i + 1
Else
flag = False
End If
End If
Wend
Get_Count = i–1
End Function
String.Trim(Str()) Converts the integer data type to a string.
String.Trim(Str()) 将整数数据类型转换为字符串。
回答by Sunny Sharma
okay, try this:
好的，试试这个：
Sub CopyName()
Worksheets("Draft").Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = Worksheets("Dashboard").Range("K2").Value
End Sub
