Excel Search Code Help

[Blake1216]

Hey,

Im trying to make a code where I can type in a number in a certain field, and then it will go into another sheet in the same book and go to that row number( I have them numbered in row A) and pull the whole row. I have it to where it works on the page where the certain field is, (I can type in the row number and it will copy from the row number I entered, and also paste into a row number that I specify in a different field) but it will only work on the sheet where the field is located. I need it to pull from a different sheet. Here is my code. (This is also my first time posting on a place like this so sorry if I was supposed to tag this a certain way or something.

Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iMiddle As Long
Dim iCntr As Long
lRow = 20
For iCntr = lRow To 1 Step -1
    If Worksheets("BLAKE").Cells(iCntr, 1) = Worksheets("MASTER").Cells(3, 18) Then
        Rows(iCntr).Copy
    End If
Next
For iMiddle = lRow To 1 Step -1
    If Cells(iMiddle, 1) = Cells(4, 18) Then
        Rows(iMiddle).PasteSpecial
    End If
Next
End Sub

Thank you,
Blake

[DaDoc]

Hey,

... but it will only work on the sheet where the field is located. I need it to pull from a different sheet. Here is my code. (This is also my first time posting on a place like this so sorry if I was supposed to tag this a certain way or something.


Sub sbDelete_Rows_Based_On_Criteria()
Dim lRow As Long
Dim iMiddle As Long
Dim iCntr As Long


lRow = 20
For iCntr = lRow To 1 Step -1
If Worksheets("BLAKE").Cells(iCntr, 1) = Worksheets("MASTER").Cells(3, 18) Then
Rows(iCntr).Copy
End If
Next
For iMiddle = lRow To 1 Step -1
If Cells(iMiddle, 1) = Cells(4, 18) Then
Rows(iMiddle).PasteSpecial
End If
Next
End Sub

Thank you,
Blake

Hi Blake,
you need to reference the cells and rows correctly - e.g:
dim oWS as worksheet, oSH as worksheet
Set oWS=worksheets("MASTER")
Set oSH=worksheets("BLAKE")
If you don't reference expressions like Cells(row,column) or rows(Rownumber) or any other object Excel-VBA will will always refer the object to the activesheet. If you want to access any other than the ActiveSheet you need to reference like: oWS.Rows(RowNumber%).copy

The first loop in your code will run down to row 1. If you want a higher row to be copied you will need to EXIT the FOR-Loop with EXIT FOR after you copied the row.