Forum Discussion
Compare, copy and paste from an external worksheet.
peiyezhu I'm using a duplicate of live data some I don't have a dummy set I'm able to share. Everything seems to work until it hits the IF statement then it checks the parent workbook for the data rather than the external, target workbook. I've tried numerous methods to point the search to the target workbook but without success.
The aim is to enter a name in a search box in a form the parent workbook, search the target workbook for a match then return the row containing the data to a blank sheet on the parent workbook.
Set Wb2 = Workbooks.Open(xpath)
Wb2.Activate
'Following IF statement not finding any search matches - searching wrong spredsheet.
For i = 1 To 200
rem Do you want to search in Sheet1 from workbook wb2?
with wb2
If .Sheet1.Cells(i, 1) = SSearch Or _
.Sheet1.Cells(i, 2) = SSearch Or _
.Sheet1.Cells(i, 3) = SSearch Or _
.Sheet1.Cells(i, 4) = SSearch Then
Wb2.Range(.Sheet1.Cells(i, 1), .Sheet1.Cells(i, 18)).Copy
ThisWorkbook.Sheets("DailySearch").Range("A200").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End If
end with
Next i
- I want to enter the search parameters into Wb1, search Sheet1 in Wb2, copy the matching rows and paste them into DailySearch (Sheet7) in Wb1. I've tried adding the With statement but the search is still being performed in Wb1.
- still being performed in Wb1
what have you got the result?
with wb2
denug.print .Sheet1.Cells(i, 1)
stop
set some break point and screen shot what you gotpeiyezhu I've tried debug and it returned the names stored in Workbook 1 - where the search is generated. No matter how I set it up to search in Workbook 2, it goes to Wb1. I thought that this should be relatively straight forward. I can't work out what's going wrong!
I haven't attached an image as it only lists the names I've identified as coming from Wb1 and, as it's based on live data, I can't share the information.