views:

1028

answers:

1

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);
+1  A: 

-- 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)
Marc
Indeed, it does. But I want to copy the sheet's content to another sheet which is in another workbook ;)
hab
Perhaps you should clarify the title to be 'Excel copy worksheet content' instead of just worksheet ;)
Marc
well, it returns 1048577
hab
I mean emptyRow = 1048577
hab
Oops... that was a bug when trying to find the empty row when no data is yet available in Book2. It is fixed now.
Marc
If you are looking for the last used cell in the target sheet, you might want to check the built-in function which does that for you:var lastCell = xlWorksheet.Cells.SpecialCells( Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell, Type.Missing);
Mathias