Forum Discussion
Compare, copy and paste from an external worksheet.
I'm trying to perform a search on an external worksheet then copy the lines with matching data to paste in the original worksheet. The code I've come up with is a bit of a mess as I've tried multiple methods and employed a mix and match process (and I'm an untalented beginner!). The main problem now seems to be with the IF statement as it's only searching the main workbook and not the external one. Any guidance anyone can offer would be gratefully received. The code I have so far is:-
Private Sub
cmbSearch_Click()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Dim X As Integer
Dim xpath As String
Dim i As Integer
Dim SSearch As String
Set Wb1 = ThisWorkbook
xpath = "\\DUCH-3900\Environment\ENV-SYS2\RSAFETY\SCHOOL CROSSING PATROL GROUP\SCP OPS Logs\2023 LOGS\TestData\Daily Log 2023.xlsx"
SSearch = (txtSearch.Text)
ThisWorkbook.Sheets("DailySearch").Range("A1:R200").ClearContents
Application.ScreenUpdating = False
For i = 1 To 200
Set Wb2 = Workbooks.Open(xpath)
Wb2.Activate
'Following IF statement not finding any search matches - searching wrong spredsheet.
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
Next i
Wb2.Close SaveChanges:=False
End Sub
15 Replies
- Can you share your files with dummy datas and expected result？
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 have a spreadsheet into which I routinely pull rows from two external sheets. I do it solely by means of a FILTER function. No VBA needed. It is necessary that the external sheets be open when I'm doing it, but other than that, the FILTER function, which contains selection criteria, does all the work.
Have you considered that possibility?
- It's all part of a larger interface, pulling data from different sheets and workbooks to a central point. I haven't considered using a filter but I'll look into it.
ThanksJust to make sure we're on the same page here, I wasn't suggesting using "a filter"--which might mean the button on the data tool bar--but rather, the FILTER function. There is a link in that earlier message to a resource that describes the function.
There's also a very useful and informative video that Microsoft used to introduce the function when it was new.
