Copy single excel row from worksheet 1 to worksheet 2

Give this a try

Dim oExcel As Object
Dim oBook As Object
Dim oSheet1, oSheet2 As Object

'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks(1)

'Add data to cells of the first worksheet in the new workbook.
oSheet1 = oBook.Worksheets(1)
oSheet2 = Workbooks(1).Worksheets(2)
oSheet2.Range("B20").Value = oSheet1.Range("A10").Value

'Save the Workbook and quit Excel.
oBook.Save
oSheet1 = Nothing
oSheet2 = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

Select allOpen in new window

Try this code:

Dim C As Long
    Dim Cell As Range
    Dim NextRow As Long
    Dim Rng As Range
    Dim EntryWks As Worksheet
    Dim DBWks As Worksheet
    C = 1
    Set EntryWks = Worksheets("sheet1")
    Set DBWks = Worksheets("sheet2")
    Set Rng = EntryWks.Range("A3:F3")
   
    'NextRow = DBWks.UsedRange.Rows.Count
    'NextRow = IIf(NextRow = 1, 1, NextRow + 1)
    NextRow = 20
   
    For Each RA In Rng.Areas
    For Each Cell In RA
    C = C + 1
    DBWks.Cells(NextRow, C) = Cell
    Next Cell
    Next RA

Hello Cyber,

I am getting the following error - "public member worksheets on type workbooks not found vb.net"
Should I be adding any references? I am using Visual Studio 2005 and have MS Office 2007. I added "Excel 12" from the references tab, but I will be comfortable if you could tell me how to add references if any!

Thanks
J

Hi there,

No references are required, but I apologize, it should be oBook.Sheets.  The name goes Workbooks, then Sheets - intuitive yes!

Cyberkiwi - Below is the error (attached) I am getting when I run the code. Please advise!
Error.JPG

OK, I don't have visual studio...I have visual studio 2005.

Awesome. I think it works great. Can I quickly ask you to add a piece of code to actually show how to use range. For eg.

I want to copy B5 thru F5 from worksheet1 and then copy that data into J7 thru N5 (assuming same number of cells) in worksheet2? Thanks bud.

Hi there,

Here's a part of it.

        oSheet1 = oExcel.Worksheets(1)
        oSheet2 = oExcel.Worksheets(2)
        oSheet1.Range("A10:A15").Copy()
        oSheet2.Activate()
        oSheet2.Range("B25").Select()
        oSheet2.Paste()

Just beware of some gotchas - you cannot range.select unless it is the active sheet
Also, range.paste actually pastes the clipboard, so if you do a select, debug, copy something to clipboard then continue to paste, it is not the content of A10:A15!

Good luck

Awesome! Thanks for the quick help....Keep up the good job!