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.

我搜索并找到了一种方法，但现在当我复制单元格时，我得到一个带有消息 将 Excel 工作表中的所有单元格粘贴到当前工作表中的 COM 异常"，您必须粘贴到第一个单元格(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# 编写的

Following is the code, it is in 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屋！