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 be updated based on a form. It updates the list with new managers by copying their predecessors and replaces a few cells with new info.
My biggest struggle is that I want the macro to be dynamically updated. It does the job now, but now only for the specific situation that I used to record the macro. Thus, I would like to use the copied value instead the one example during recording.
How do I change the OLD MANAGER to the actual value that is inside the cell (this changes every time a new form is filled in)?
Also how do I dynamically copy the value 'Kyo' (that changes every form) inside the filter in the other document by using a text filter that contains the copied value?
Currently I am working on a macro that should be updated based on a form. It updates the list with new managers by copying their predecessors and replaces a few cells with new info.
My biggest struggle is that I want the macro to be dynamically updated. It does the job now, but now only for the specific situation that I used to record the macro. Thus, I would like to use the copied value instead the one example during recording.
How do I change the OLD MANAGER to the actual value that is inside the cell (this changes every time a new form is filled in)?
Also how do I dynamically copy the value 'Kyo' (that changes every form) inside the filter in the other document by using a text filter that contains the copied value?
Excel Formula:
Sub FLM_4()
'
' FLM_4 Macro
'
'
Windows("Book1").Activate
Range("C17").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Site overview (003).xlsx").Activate
ActiveSheet.Range("$B$3:$AS$66").AutoFilter Field:=1, Criteria1:= _
"=*Kyoc*", Operator:=xlAnd
Application.WindowState = xlNormal
Windows("Book1").Activate
Range("C19").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Site overview (003).xlsx").Activate
ActiveSheet.Range("$B$3:$AS$66").AutoFilter Field:=2, Criteria1:= _
"=OLD MANAGER", Operator:=xlAnd
Rows("4:4").Select
Application.CutCopyMode = False
Selection.Copy
Selection.Insert Shift:=xlDown
Range("C4").Select
Application.CutCopyMode = False
Selection.Copy
Range("E4").Select
ActiveSheet.Paste
Range("D4").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TODAY()"
Range("D4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("Book1").Activate
Range("C11").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Site overview (003).xlsx").Activate
Range("F4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.WindowState = xlNormal
Windows("Book1").Activate
Range("C13").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Site overview (003).xlsx").Activate
Range("C4").Select
ActiveSheet.Paste
End Sub