How can I get the filter to search for a criteria which will be dependant on what is referenced in a cell?

[Monique76]

Dear Forum,

I am fairly new to VBA and I have been searching the forum for days now but have not been succceful so far in reaching a solution - so any help is much appreciated.

I am trying to put togheter a macro that will copy information from one row in a worksheet (input data sheet) over to another sheet (contract list).

I need to find the row in the contract list which contains the value in cell d2 on the (input data sheet), based on the input value (which wil vary with all sorts of numbers).

I need the macro to go to the contract list and search colum D for the value that has been input in cell D2 and then search the sheet (contract list) to find the row which contain this value , I then need to copy row 2:2 of the (input sheet) and replace the information that is contained in the row with the matching search on the sheet (contract list).

I tried to record the macro to see if I could alterate it - but I am lost on how to set the criteria ?
This is what it looks like :

Sheets("Input data sheet").Select
Range("D2").Select

Selection.Copy
' here comes the criteria for filetring



Sheets("Contract List").Select
ActiveSheet.ListObjects("List1").Range.AutoFilter Field:=4, Criteria1:= _
"=456789", Operator:=xlAnd

'here the criteria 456789 needs to be variable with what number has been input in cell D2 on the data inout sheet.

Sheets("Input data sheet").Select
Rows("2:2").Select

Selection.Copy
Sheets("Contract List").Select
Rows("3:3").Select

'row 3:3 will vary as the filter will find the row were the information is contained

Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveSheet.ListObjects("List1").Range.AutoFilter Field:=4

can anyone please help on how I can get the macro to perform this in the middle as the macro is already performing some copying taske before I get to this point????

 

[didIDo_that]

Hopefully this will help. I believe you can just replace the variable with the decleartion and the string directly below. the line where the visible cells are copied can be used to copy from the rows that match your criteria, or you can adapt that line to paste into the row that you need the info pasted into.


'Declare the variable and make it equal to cell D2
Dim inputData As String
inputData = Sheets("input data sheet").Range("D2")
'Im guessing row 1 is a header and does not need to be copied
'in that case I would use the below code to select the correct row
'I may have missed it, I'll assume you are using 2003

'this should work if you do not have data below your table in column A and only 1 row meets your filter criteria
Sheets("Contract List").Range("A2:IV" & Sheets("Contract List").Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy

 

[Monique76]

Thank you so much for your reply, much appreciated!
and it works to some extend,
but It does not seem to carry over (copy) the information from row 2 on the data inputsheet) to the row it has found.

It actully seems to copy the information on the Contract data sheet as opposed to the data in row 2 from the (Data input sheet).

What I am trying to do is actually copy row2 (on data input sheet) and overwrite the found row in the contract list .

You see I have made an input sheet so it is easier for the user to update information which is actualy contained in the table in the contract list - so I need to overwrite the information contained in (contract list) it everytime they input new information on the input sheet. (if that makes any sense?????

PS I am actually using 2007

 

[didIDo_that]

I think this should work like you want it. This will filter the list for what is in D2 on the input data sheet. Then it will copy the entire row for D2 and paste it into the filtered row on the contract list sheet. Be sure that the sheet names in the code agree with your sheet names.

Dim inputData As String
inputData = Sheets("input data sheet").Range("D2")
ActiveSheet.ListObjects("List1").Range.AutoFilter Field:=4, Criteria1:= _
inputData, Operator:=xlAnd

Sheets("Input data sheet").Range("D2").EntireRow.Copy

Sheets("Contract List").Range("A2:A" & Sheets("Contract List").Range("D1048576").End(xlUp).Row).SpecialCells(xlCellTypeVisible).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

 

[Monique76]

That is excellent !!Thank you so much for your help!! it works perfect now