Link to home
Start Free TrialLog in
Avatar of daintysally
daintysallyFlag for United States of America

asked on

Use vba to open excel 2007 file and copy the first row

Hi Experts,

How do I programmatically open an excel file from a saved location and then copy the first row of data and paste the values into column B of another worksheet?  I would like to be able to do this using a form button in excel 2007.  Any help would be greatly appreciated.  Please see the sample files attached. Test.xlsx
Test2.xlsx
Avatar of experts1
experts1

Use code below in bottun click procedure of your Form button:


Sub CopyFromBook()

    'Open target workbook here
    Workbooks.Open Filename:= "C:\MyWorkbook.xls"

    'Select the Row to copy from here (First Row only)
    Rows("1:1").Select

    'Copy contents of the selected Row here
    Selection.Copy

    'Activate the Original WorkBook here
    Windows("OriginalWorkBook").Activate

    'Select the Row to paste into from here (First Row of original book)
    Rows("1:1").Select

    'Paste into selected cell here
    ActiveSheet.Paste

    'Close target workbook here
    Windows("MyWorkbook").Close
End Sub
Avatar of daintysally

ASKER

I want to paste the values into column B of the excel spreadsheet instead of pasting it to another row.  Can I transpose the row values into single column values per cell?
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If you need to copy and paste to a single cell or range of cells example "A1", then

replace "Rows("1:1").Select" with Range(("A1").Select"

The same as above to select cell or range to paste into!.
Thank you!!! This worked perfectly!! :):)
SiddharthRou's solution was very much
similar to mine, but I guess you did not test
my solution which was submitted first?
Yes, you are correct, your solution was very similar...however, I responded asking to paste the values in a column instead of another row and SiddharthRou responded first with a complete solution.  I meant no harm...please forgive me if I have offended you in any way.  Is there anything that I can do to give you some of the points?

I just wanted to know if you had tested  my solution!

Also, I guess the points have already been allocated, so
nothing to do here.

Regards