none
C# How to copy a row from one sheet to another?

    Question

  • Hi all

    I'm trying to copy and entire row from one spreadsheet to another in the same workbook and ideas?

        _expandedRow = 2;
        Range row = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        Range nextRow = othersheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        row.Insert(XlInsertShiftDirection.xlShiftDown, nextRow);

    looks simple but just gives me a blank row?

    help please

    Tom

     

    • Moved by Cindy Meister MVPMVP Friday, November 19, 2010 7:15 PM not VSTO-specific (From:Visual Studio Tools for Office)
    Friday, November 19, 2010 3:28 PM

Answers

  • It would seem that the CopyOrigin parameter is supposed to be an enumeration
    of the type Microsoft.Office.Interop.Excel.XlInsertFormatOrigin .

    Member name                         Description
    xlFormatFromLeftOrAbove        From cells above and/or to the left.
    xlFormatFromRightOrBelow      From cells below and/or to the right.

    so its the format of the cells no copy data (Great name Microsoft)! So the solution I came up with was -

        _expandedRow = 2;

        FinalWorkbook = Application.ActiveWorkbook;
        Worksheet activesheet = (Worksheet)FinalWorkbook.ActiveSheet;
        Worksheet othersheet = (Worksheet)FinalWorkbook.Sheets[activesheet.Index + 1];

        Range row = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        Range nextRow = othersheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        row.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);


        Range newRow = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;

        newRow.Value2 = nextRow.Value2;

     

     

     

    • Marked as answer by Tommy_Tucker Monday, November 22, 2010 2:54 PM
    Monday, November 22, 2010 2:54 PM

All replies

  • Oh and by the way

        FinalWorkbook = Application.ActiveWorkbook;
        Worksheet activesheet = (Worksheet)FinalWorkbook.ActiveSheet;
        Worksheet othersheet = (Worksheet)FinalWorkbook.Sheets[activesheet.Index + 1];

    Friday, November 19, 2010 3:32 PM
  • Hi Tommy

    As this question is more Excel- than VSTO technology related I'm going to move it to the Excel Developer forum where you'll find more application specialists...


    Cindy Meister, VSTO/Word MVP
    Friday, November 19, 2010 7:15 PM
  • Thanks for that for moving the thread I hope someone can help it must be something simple I'm missing

        _expandedRow = 2;

        FinalWorkbook = Application.ActiveWorkbook;
        Worksheet activesheet = (Worksheet)FinalWorkbook.ActiveSheet;
        Worksheet othersheet = (Worksheet)FinalWorkbook.Sheets[activesheet.Index + 1];

        Range row = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        Range nextRow = othersheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        row.Insert(XlInsertShiftDirection.xlShiftDown, nextRow);
    Monday, November 22, 2010 10:47 AM
  • Hi Tommy

    Well, I'm not an expert in Excel but for what it's worth, your code doesn't show you're copying anything. You're making room (row.Insert), but I can't see that you're actually putting anything in there.

    This appears to work for me:

      nextRow.Value2 = row.Value2;

    As do the Copy and PasteSpecial methods.


    Cindy Meister, VSTO/Word MVP
    Monday, November 22, 2010 11:18 AM
  • object Insert([In, Optional] object Shift, [In, Optional] object CopyOrigin);

    Parameters

    Shift

    Optional Object . Specifies which way to shift the cells. Can be one of the following XlInsertShiftDirection constants: xlShiftToRight or xlShiftDown . If this argument is omitted, Microsoft Excel decides based on the shape of the range.

    CopyOrigin

    Optional Object . The copy origin.

     

    The above is taken from the help on Range.Insert(). Therefore I was trying to give CopyOrigin the nextRow which has the data I want copied into the newly inserted row. If this not what CopyOrigin for then what is for?

    if I use row.Value2 = nextRow .Value2 won't this just overwrite what is in row and not insert nextRow in the blank row created.

     

     

    Monday, November 22, 2010 12:03 PM
  • Hi Tommy

    About CopyOrigin, I don't know, although I suspect it might copy the row you're shifting.

    As to my suggestion, you put that AFTER the Insert method (into the new, empty row)


    Cindy Meister, VSTO/Word MVP
    Monday, November 22, 2010 12:12 PM
  • Tommy : You need two statements.  One is a Copy and then a second Insert.  The Insert object uses the Clip Board and the Copy put the data into the Clip board.  You original code doesn't have a COPY method.
    jdweng
    Monday, November 22, 2010 12:20 PM
  • It would seem that the CopyOrigin parameter is supposed to be an enumeration
    of the type Microsoft.Office.Interop.Excel.XlInsertFormatOrigin .

    Member name                         Description
    xlFormatFromLeftOrAbove        From cells above and/or to the left.
    xlFormatFromRightOrBelow      From cells below and/or to the right.

    so its the format of the cells no copy data (Great name Microsoft)! So the solution I came up with was -

        _expandedRow = 2;

        FinalWorkbook = Application.ActiveWorkbook;
        Worksheet activesheet = (Worksheet)FinalWorkbook.ActiveSheet;
        Worksheet othersheet = (Worksheet)FinalWorkbook.Sheets[activesheet.Index + 1];

        Range row = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        Range nextRow = othersheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;
        row.Insert(XlInsertShiftDirection.xlShiftDown, Missing.Value);


        Range newRow = activesheet.get_Range( "A" + _expandedRow, Missing.Value).EntireRow;

        newRow.Value2 = nextRow.Value2;

     

     

     

    • Marked as answer by Tommy_Tucker Monday, November 22, 2010 2:54 PM
    Monday, November 22, 2010 2:54 PM