Excel Macro - How to filter on a copied value that changes

[Excelquestion35]

Hi all,

Currently, I am working on a macro that should update a master file. In this master file a new row should be added with old data (i.e. a row with the right characteristics that will be copied and inserted as a new row) and updated with data from another form. The essence is that master file keeps track of all current first line managers. The form is used to communicate changes in first line managers.

For instance, when I have John replacing Maria that both work in operation Kellogs. A form will be filled in by the Kellogs operation and giving details about John and who he will replace.

For this I want to create a macro. My macro will first go the form and then see that Maria from Kellogs will be replaced. Thus, the macro should copy the value 'Kellogs' and use it as a textfilter in column B in the master file to only find the first line managers of Kellogs. The same steps have to repeated for finding Maria in column C.
When her row (as a first line manager for Kellogs) is found by the macro. The macro should copy the entire row, insert the row and update some of the cells in the different columns for this new row (the name of John, his ID, state that John is the succesor of Maria) etc.

My question is however:
- How can I filter inside a macro on dynamic value (thus the one that I copied) that changes every time a form is filled in? This time it is Kellogs but next time it can be Heinz for example.
- Same question for the case that is not Maria but another First line manager?
- As you can see in the code below, the filter uses *'s for using the contains text filter. How can I still use the contains text filter but then with a value that changes every time a new form is filled in?

I pasted below the code of the filtering, as you can see it will always look for this specific value, but this should be changed based on what is inside cells (C17 & C18):

Sub FLM_1()
'
' FLM_1 Macro
'

'
    Sheets("FLM-change").Select
    Range("C17").Select
    Selection.Copy
    Sheets("FLMs").Select
    ActiveSheet.Range("$B$3:$AS$66").AutoFilter Field:=1, Criteria1:= _
        "=*Kellogs*", Operator:=xlAnd
    Sheets("FLM-change").Select
    Range("C18").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("FLMs").Select
    ActiveSheet.Range("$B$3:$AS$66").AutoFilter Field:=2, Criteria1:= _
        "=Maria", Operator:=xlAnd
End Sub

 

[offthelip]

When using VBA to do things you can achieve the same effect as filtering by using a loop to find the row you are looking for, This is usually much easier to deal with with vba than trying to do it with filtering
This code should select the row which matches the manager and operations athat are given in C17 and C18:

Sub test()
inarr = ActiveSheet.Range("$B$1:$AS$66") ' load all the data into a variant array NOTE starting from row1
manager = Range("C17")
ops = Range("C18")
For i = 3 To 66   ' start loop at row 3
   If inarr(i, 1) = manager And inarr(i, 2) = ops Then ' we have found the row
    Range(Cells(i, 2), Range(i, 45)).Select ' do whatewver you need to do on this row
    Exit For
   End If
Next i
End Sub

 

[Excelquestion35]

Thank you this helped a lot!