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
