Copy/Paste & Find/Replace

[vjimen2]

Hello,

I'm still learning a lot about macros and I've come across one I need help with. Here's what I'm trying to accomplish;
On one tab I would like to have a macro that looks at an abbreviated date, that will change from month to month and be in a different column, Ex: "Jun" in cell L5 one month but "Aug" in column Q5 the next month.
(There is a static reference for this date in cell A5 that I will manually update as a check each month.)
The macro will grab the data from the previous month Ex: L80:O395 and copy paste under the Aug date in cells Q80:T395.
After the copy/paste has been completed the formulas in those cells Q80:T395 need to be updated to reflect the abbreviated month. Ex: SUMIF('PL by cust Jun 20'!$A$4:$A$184,'2020_Prod'!$B80,'PL by cust Jun 20'!$B$4:$B$184) where "Jun" is replaced with "Aug".
That should complete the macro and save a lot of manual updates each month.
Thanks for the help!

 

[vjimen2]

Here is the code I have used already and I have been trying to modify and make work.

Sub CopyPasteFindReplace()
'
' CopyPasteFindReplace Macro
'

'
Range("G80:J80").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-309
Range("L80").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Application.CutCopyMode = False
Range("A5").Select
Selection.Copy
Range("L80:O391").Select
Selection.Replace What:="Jun", Replacement:="Jul", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

The problem is, its not dynamic and will only copy/paste in the same cells that the original code is in. I need the code to copy/paste under the new date, in this case "Jul" and then update the formula to include the new abbreviated date. There are formulas in cells G80:J391, this is the data I want copied over to cells L80:O391. The date reference is in cell L5 for the new date and next month will be in cell Q5 for Aug. When I run the macro I would like it recognize the date I manually put in cell A5, which will be the current month. I want the copy/paste find/replace to be entered in the corresponding cells below the current month reference. Hopefully this helps explain a little more. I appreciate the help!