Excelquestion35
Board Regular
- Joined
- Nov 29, 2021
- Messages
- 53
- Office Version
- 2016
- Platform
- Windows
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):
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):
VBA Code:
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