Copy single excel row from worksheet 1 to worksheet 2

Hello experts,

I would like to copy an excel row (A10) from worksheet 1  to a row (B20) in worksheet 2 both in the same excel file using vb code in visual studio 2005. Thanks!
DoofuSAsked:
Who is Participating?
 
cyberkiwiCommented:
Hi there,

Give this a try instead, I ran it in VB and it worked ok linking oExcel to worksheet directly.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet1, oSheet2 As Object

        'Start a new workbook in Excel.
        oExcel = CreateObject("Excel.Application")
        'oExcel.Visible = True
        oBook = oExcel.Workbooks.Open("C:\book1.xls")

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

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

Open in new window

0
 
cyberkiwiCommented:
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()

Open in new window

0
 
Sarika30Commented:
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
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
DoofuSAuthor Commented:
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
0
 
cyberkiwiCommented:
Hi there,

No references are required, but I apologize, it should be oBook.Sheets.  The name goes Workbooks, then Sheets - intuitive yes!
0
 
DoofuSAuthor Commented:
Cyberkiwi - Below is the error (attached) I am getting when I run the code. Please advise!
Error.JPG
0
 
DoofuSAuthor Commented:
OK, I don't have visual studio...I have visual studio 2005.
0
 
DoofuSAuthor Commented:
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.
0
 
cyberkiwiCommented:
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
0
 
DoofuSAuthor Commented:
Awesome! Thanks for the quick help....Keep up the good job!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.