daintysally
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!.
replace "Rows("1:1").Select" with Range(("A1").Select"
The same as above to select cell or range to paste into!.
ASKER
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?
similar to mine, but I guess you did not test
my solution which was submitted first?
ASKER
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
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"
'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").Clos
End Sub