Excel script using if statement to copy/paste special values for specific rows

[kcampbell675]

Hello, I am attempting write a script to "lock down" values (e.g. copy / paste special values) and eliminate the current formula. I only want this action to apply to monthly values (col. AF - AQ) that are in rows that have "Q1 FRCST", col G when our forecast is finalized. I am just starting to learn about Excel scripts. Am I even able to do this as a script? I would like to automate this process; there are hundreds of rows impacted. I am open to other suggestions. Thank you!

 

[bobaol]

try this... assume it will repeat 24 times. change the 24 if you want. this is all done using Record Macro, except the line of For i = 1 to 24, and the line of Next. I find Record Macro is much easier than writing script. and it works for me 90% of the time. also, i like using Selection.Copy so that when i run the code line-by-line, it is an easy way to see which cell I am in. cheers!

Sub Macro1FIND_ALTESV()
''GO TO G1 FIRST
    Application.Goto Reference:="R1C7"
''Select column G, find your item
    ActiveCell.Columns("A:A").EntireColumn.Select
    Selection.Find(What:="Q1 FRCST", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(0, 1).Range("A1").Select
    Selection.Copy

'''go to AF
    ActiveCell.Offset(0, 24).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
''copy AF to AQ
    ActiveCell.Range("A1:L1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
''paste As Values
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
'''move over one cell.  now repeat 24 times

For i = 1 To 24
    ActiveCell.Offset(0, 1).Range("A1").Select
    Cells.Find(What:="Q1 FRCST", After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Offset(0, 25).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    ActiveCell.Range("A1:L1").Select
    Application.CutCopyMode = False
    Calculate
    Selection.Copy
    Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Next
End Sub

 

[kcampbell675]

Thank you!