Excel copy worksheet

Question

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.

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

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);

Worksheets have a finite size, so if you are copying whole worksheets, there is no room to "append" more than one to a single worksheet. — wakingrufus

Marc Marc · Accepted Answer · 2009-10-21T09:50:49

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

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:

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

Excel copy worksheet

1 Answers