MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying a range to another workbook range/rows


Posted by Greg Woods on August 30, 2001 7:43 AM

What I'm trying to do is open another workbook and loop through the first column of cell until I find the cell with my name. Then I want to copy that row into another workbook. Here's my code. Sub Search()

Dim xlObject
Dim xlBook
Dim xlSheet
Dim MyRange As Range

Dim SheetNum As String
SheetNum = InputBox("Enter Date")


Set xlObject = CreateObject("Excel.Application")
Set xlBook = xlObject.Workbooks.Open("C:\WINNT\Profiles\gwoods\Desktop\Daily agents report.xls")
Set xlSheet = xlBook.Sheets


For Each sht In xlBook.Worksheets
If sht.Name = SheetNum Then
Set MyRange = Worksheets("Sheet1").[A2:K2]
For Each c In sht.Range("A1:A126").Cells
If (c.Value) = ("Greg Woods") Then MyRange = ActiveCell.Row???? <-------- this is the part in question
I need to make MyRange = The row that contains the cell with the value of Greg Woods.




Next
End If
Next sht

xlBook.Close
xlObject.Quit


Posted by Barrie Davidson on August 30, 2001 8:00 AM

Next

Try using

If (c.Value) = ("Greg Woods") Then MyRange = ActiveCell.EntireRow.Address

I haven't tested it, but I think it will work for you.

Regards,
Barrie

Posted by Greg qoods on August 30, 2001 3:01 PM

For Each sht In xlBook.Worksheets If sht.Name = SheetNum Then Set MyRange = Worksheets("Sheet1").[A2:K2] For Each c In sht.Range("A1:A126").Cells If (c.Value) = ("Greg Woods") Then MyRange = ActiveCell.Row???? <-------- this is the part in question I need to make MyRange = The row that contains the cell with the value of Greg Woods. Next End If Next sht : xlBook.Close xlObject.Quit Try using If (c.Value) = ("Greg Woods") Then MyRange = ActiveCell.EntireRow.Address I haven't tested it, but I think it will work for you. Regards,

Well that didnt work. But it sure looked like it would. I just filled the row with garble. Any other suggestions. This is what I get for straying from C++

Posted by Barrie Davidson on August 31, 2001 7:25 AM

Sorry Greg (not thinking clearly yesterday), try this instead

If c.Value = "Greg Woods" Then MyRange = ActiveCell.EntireRow.Address

Barrie