VBA-Search row, create formula below found “search” name in header, copy formula down to last row

[deniseturan]

Starting in cell B5, search ROW for "Current Month". In this column starting in cell below "Current Month" Header (L6 this time), create a formula that references one column to left of this column, "=K6".This formula will continue down the column until the last row. This column will change every month due to the fact that the newest month financial results will be added every month. Currently, MainAccount, DEPT, Dep Lookup, PROD, PROD_LINE, RD_CAT, January, February, March, April, Current Month, and YTD are the current headers as I am working on results for April month end. "Current Month" is currently in column L of sheet, next month, it will be in M. Basically, this is providing me the current months charges that will be used to update a variety of pivot tables where I don't have to change the monthly data every month.I think I am getting tripped up at the ActiveCell portion of my code, but I am not sure.​

<code style="background-color: rgb(239, 240, 241); border-bottom-color: rgb(57, 51, 24); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(57, 51, 24); border-left-style: none; border-left-width: 0px; border-right-color: rgb(57, 51, 24); border-right-style: none; border-right-width: 0px; border-top-color: rgb(57, 51, 24); border-top-style: none; border-top-width: 0px; box-sizing: border-box; font-family: Consolas,Menlo,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New,monospace,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 16.9px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline; white-space: pre;">

  Range [/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"B5"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]                                                           Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Selection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Selection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xltoRight[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])).[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]                               
Selection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Find[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]What[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"Current Month"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] After[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] LookIn[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] _
    xlFormulas[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] LookAt[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]xlPart[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] SearchOrder[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]xlByRows[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] SearchDirection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] _
    xlNext[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] MatchCase[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]False[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] SearchFormat[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]False[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Activate                     
ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Offset[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]                                                     
ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"=RC[-1]"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]                                              

LR2 [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Cells[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Rows[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Count[/FONT][/COLOR][COLOR=#303336][FONT=Consolas],[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] [/FONT][/COLOR][COLOR=#7D2727][FONT=Consolas]"A"[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Row
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Set[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Rng2 [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Range[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ActiveCell [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]&[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] LR2[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

ActiveCell[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Copy
Rng2[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Paste

</code>

In the Current Month Column, there should be a formula all the way down the page that references the last month. In this case, Current Month header is located in L5, the formula would start in L6, which would be =k6, and then copied down to last row, and it would only copy formula in empty rows only so that I don't lose my headers as there are many ranges on sheet that are tied to pivot tables.​

 

[Fluff]

Cross posted https://stackoverflow.com/questions...-found-search-name-in-header-copy-formula-dow

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.