vba Excel 复制工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1599715/
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 worksheet
提问by akif
In Excel VBA (or if you could in C#, I'm using the Excels Object Library from .NET), how to copy a worksheet from one workbook to another sheet in another workbook. Basically, what I'm doing is copying every of my sheet into a central worksheet in another workbook and then will do all the stuff I need to do there. I tried using Range.Copy method, I gave the Destination parameter as the range of the other workbook. It worked perfectly, but there is one problem, that is every time I copy it replaces the older data in that worksheet. How do I do something like so that when I paste it pastes in the end of the sheet.
在 Excel VBA（或者如果你可以在 C# 中，我正在使用 .NET 中的 Excels 对象库）中，如何将工作表从一个工作簿复制到另一个工作簿中的另一个工作表。基本上，我正在做的是将我的每个工作表复制到另一个工作簿中的中央工作表中，然后将在那里完成我需要做的所有事情。我尝试使用 Range.Copy 方法，我将 Destination 参数作为其他工作簿的范围。它工作得很好，但有一个问题，就是每次我复制它时都会替换该工作表中的旧数据。我如何做这样的事情，以便当我粘贴它时，它会粘贴在工作表的末尾。
EDIT:I searched and found a way, but now when I copy the cells I get a COM exception with the message "To paste all cells from an Excel worksheet into the current worksheet, you must paste into the first cell (A1 or R1C1)."
编辑：我搜索并找到了一种方法，但是现在当我复制单元格时，我收到一个 COM 异常消息“要将 Excel 工作表中的所有单元格粘贴到当前工作表中，您必须粘贴到第一个单元格（A1 或 R1C1） .”
Following is the code, it is in C#
以下是代码，它在C#中
logWorksheet = logWorkbook.ActiveSheet as Excel.Worksheet;
Excel.Range tempRange = logWorksheet.Cells[logWorksheet.Rows.Count, "A"] as Excel.Range;
tempRange = tempRange.get_End(Excel.XlDirection.xlUp);
int emptyRow;
if (tempRange.Row > 1)
emptyRow = tempRange.Row + 1;
else
emptyRow = tempRange.Row;
string copyLocationAddress = Convert.ToString(emptyRow);
Excel.Range copyLocation = logWorksheet.get_Range(
"A" + copyLocationAddress, Type.Missing) as Excel.Range;
// copy whole workbook to the central workbook
tempLogSheet.Cells.Copy(copyLocation);
回答by Marc
-- UPDATE --
- 更新 -
This snippet copies the cells A1:A3 of Book1 to Book2. It will find the last used cell in Book2 and will append the data underneath it.
此代码段将 Book1 的单元格 A1:A3 复制到 Book2。它将在 Book2 中找到最后使用的单元格，并将数据附加到它下面。
Sub CopyRange()
Dim source As Worksheet
Dim destination As Worksheet
Dim emptyRow As Long
Set source = Workbooks("Book1.xlsx").Sheets("Sheet1")
Set destination = Workbooks("Book2.xlsx").Sheets("Sheet1")
'find empty row (actually cell in Column A)'
emptyRow = destination.Cells(destination.Rows.Count, 1).End(xlUp).Row
If emptyRow > 1 Then
emptyRow = emptyRow + 1
End If
source.Range("A1:A3").Copy destination.Cells(emptyRow, 1)
End Sub
-- OLD --
- 老的 -
This sample copies all the sheets of Book1.xlsx to Book2.xlsx:
此示例将 Book1.xlsx 的所有工作表复制到 Book2.xlsx：
Workbooks("Book1.xlsx").Worksheets.Copy Before:=Workbooks("Book2.xlsx").Sheets(1)
