Excel复制工作表 [英] Excel copy worksheet

问题描述

在Excel VBA中（或者，如果使用C＃，我正在使用.NET中的Excels对象库），如何将工作表从一个工作簿复制到另一工作簿中的另一工作表。基本上，我正在做的是将我的每张工作表都复制到另一个工作簿的中央工作表中，然后将在那里做所有我需要做的事情。我尝试使用Range.Copy方法，将Destination参数指定为其他工作簿的范围。它工作得很好，但是有一个问题，那就是我每次复制它都会替换该工作表中的旧数据。我该怎么做，以便粘贴时将其粘贴到工作表的末尾。

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.

编辑：我搜索并找到了一种方法，但是现在复制单元格时，出现COM异常，并显示消息要将Excel工作表中的所有单元格粘贴到当前工作表中，必须粘贴到第一个单元格（A1或R1C1）。

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)."

以下是代码，在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);

推荐答案

-更新-

此代码段将Book1的单元格A1：A3复制到Book2。它将在Book2中找到最后使用的单元格，并将数据附加在其下面。

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.

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

-老-

此示例将Book1.xlsx的所有工作表复制到Book2.xlsx：

This sample copies all the sheets of Book1.xlsx to Book2.xlsx:

Workbooks("Book1.xlsx").Worksheets.Copy Before:=Workbooks("Book2.xlsx").Sheets(1)

这篇关于Excel复制工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！