excel vba复制黏贴_如何在Excel VBA中复制行-蒲公英云

excel vba复制黏贴

Using VBA to program Excel isn’t as popular as it once was. However, there are still plenty of programmers who prefer it when working with Excel. If you are one of those people, this article is for you.

使用VBA对Excel进行编程并不像以前那样流行。但是，仍然有很多程序员在使用Excel时更喜欢它。如果您是其中的一员，那么本文适合您。

Copying a row in Excel VBA is the kind of thing that Excel VBA is really useful for. For example, you may want to have one file of all your receipts with date, account, category, provider, product/service, and cost entered one line at a time, as they occur—an instance of evolving accounting rather than static accounting. To do this, you need to be able to copy a row from one worksheet to another.

在Excel VBA中复制行是Excel VBA真正有用的功能。例如，您可能希望在所有收据中都包含一个文件，其中日期，帐户，类别，提供者，产品/服务和成本在发生时每次输入一行，这是不断发展的会计而非静态会计的一种情况。为此，您需要能够将一行从一个工作表复制到另一个工作表。

A sample Excel VBA program that copies a row from one worksheet to another—using only three columns for simplicity—contains:

一个示例Excel VBA程序，它将一行从一个工作表复制到另一个工作表(为简单起见，仅使用三列)包含：

An alpha column for text

文字的Alpha栏
A numeric column - an automatic sum is created on the target worksheet

一个数字列-在目标工作表上创建一个自动求和
A date column - the current date and time is filled in automatically

日期列-当前日期和时间会自动填写

编写Excel VBA代码的注意事项 ( Considerations for Writing Excel VBA Code )

To trigger an event that copies the row, go with the standard—a Button form control. In Excel, click Insert on the Developer tab. Then, select the Button form control and draw the button where you want it. Excel automatically displays a dialog to give you a chance to select a macro triggered by the click event of the button or to create a new one.

要触发复制行的事件，请使用标准按钮按钮控件。在Excel中，在“开发人员”选项卡上单击“插入”。然后，选择“按钮”窗体控件并在所需位置绘制按钮。 Excel自动显示一个对话框，使您有机会选择由按钮的click事件触发的宏或创建新的宏。

There are several ways to find the last row in the target worksheet so the program can copy a row at the bottom. This example chooses to maintain the number of the last row in the worksheet. To maintain the number of the last row, you have to store that number somewhere. This might be a problem because the user might change or delete the number. To get around this, place it in the cell directly underneath the form button. That way, it’s inaccessible to the user. (The easiest thing to do is enter a value in the cell and then move the button over it.)

有几种方法可以找到目标工作表中的最后一行，因此程序可以在底部复制一行。本示例选择维护工作表中最后一行的编号。要保持最后一行的编号，您必须将该编号存储在某个位置。这可能是一个问题，因为用户可能会更改或删除该号码。要解决此问题，请将其放置在“表单”按钮正下方的单元格中。这样，用户将无法访问它。 (最简单的操作是在单元格中输入一个值，然后将按钮移到它上面。)

使用Excel VBA复制行的代码 ( Code to Copy a Row Using Excel VBA )

Sub Add_The_Line() Dim currentRow As Integer Sheets("Sheet1").Select currentRow = Range("C2").Value Rows(7).Select Selection.Copy Sheets("Sheet2").Select Rows(currentRow).Select ActiveSheet.Paste Dim theDate As Date theDate = Now() Cells(currentRow, 4).Value = CStr(theDate) Cells(currentRow + 1, 3).Activate Dim rTotalCell As Range Set rTotalCell = _ Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) rTotalCell = WorksheetFunction.Sum _ (Range("C7", rTotalCell.Offset(-1, 0))) Sheets("Sheet1").Range("C2").Value = currentRow + 1 End Sub

This code uses xlUp, a “magic number,” or more technically an enumerated constant, which is recognized by the End method. Offset(1,0) simply moves up one row in the same column, so the net effect is to select the last cell in column C.

这段代码使用xlUp(一个“幻数”，或更严格地说是一个枚举的常量)来识别，该常量由End方法识别。偏移量(1,0)只是在同一列中向上移动了一行，因此最终结果是选择了列C中的最后一个单元格。

In words, the statement says:

换句话说，该声明说：

Go to the last cell in column C (equivalent to End+Down Arrow).

转到列C中的最后一个单元格(等效于“结束+向下箭头”)。
Then, go back up to the last unused cell (equivalent to the End+Up Arrow).

然后，返回到最后一个未使用的单元格(相当于“结束+向上箭头”)。
Then, go up one more cell.

然后，再增加一个单元格。

The last statement updates the location of the last row.

最后一条语句更新最后一行的位置。

VBA is probably harder than VB.NET because you have to know both VB and Excel VBA objects. Using xlUP is a good example of the kind of specialized knowledge that is critical to being able to write VBA macros without looking up three different things for every statement you code. Microsoft has made great progress in upgrading the Visual Studio editor to help you figure out the correct syntax, but the VBA editor hasn’t changed much.

VBA可能比VB.NET难，因为您必须了解VB和Excel VBA对象。使用xlUP是这类专业知识的一个很好的例子，这对于能够编写VBA宏而无需为您编写的每个语句查找三个不同的事物至关重要。 Microsoft在升级Visual Studio编辑器以帮助您找出正确的语法方面取得了很大的进步，但是VBA编辑器并没有太大变化。