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.
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
So that reference needs to be pointed to the external.....have you investigated the INDIRECT function? It's fine so long as you don't have a lot of them in your workbook....it's one of the family called "volatile" functions, meaning it gets recalculated anytime anything gets calculated, so can use a lot of computer resources and slow things down a lot.
- I haven't used INDIRECT before. I'll read up on it and see if I can use it to get everything running. Thanks.
