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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.