Insert a new row below text and formatting Macro issue

Hi All

I'm having difficulty with a particular macro and wondered if anyone can help.

The workbook has 2 tabs
1) NEW
2) RATES

The 'NEW' tab has 5 blue buttons along the top (Labour, Plant, Materials, Other, OHP) that should each have the following function -

1) Find the word associated with the button

2) Insert a row BELOW this word
-Problem 1 - This function doesn't seem to work properly, it finds the word but then when i insert the row below, the code just picks up on the actual row number.
-Problem 2 - When i insert the row the format is different as Col A to E should be merged.

3) In this new cell below the correct word I insert a list from the 'RATES' tab

4) A VLOOKUP then adds the appropriate rate for this item and a few other bits.


The main issue i'm having is that when recording my macro the code picks up on the specific row i choose (i.e. Row 11, Row 12) instead of the one under the right word for the button pressed. Therefore if i have already inserted rows on the sheet, the new line comes under the wrong category.

It might be easier to look through it and i'll try to answer any question.

I've only added the Labour and Plant macro at present (CODE below). If you press the Plant button once and the Labour button once the rows are added into the right place. Then if you press Plant button again the row is in the wrong place.

Really appreciate any help.

David
(A newby here and to Macros)

Sub LABOUR()
'
' LABOUR Macro
'

'
    Cells.Find(What:="LABOUR", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows("9:9").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("10:10").Select
    Selection.Copy
    Rows("9:9").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A9:E9").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=RATES!$A$2:$A$6"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("G9").Select
    ActiveCell.FormulaR1C1 = "HRS"
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-7]C[-7]:R[-3]C[-6],2,FALSE)"
    Range("I9").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
    Range("I10").Select
End Sub
Sub PLANT()
'
' PLANT Macro
'

'
    Cells.Find(What:="PLANT", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows("11:11").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("12:12").Select
    Selection.Copy
    Rows("11:11").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A11:E11").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=RATES!$D$2:$D$10"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("G11").Select
    ActiveCell.FormulaR1C1 = "HRS"
    Range("H11").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-9]C[-4]:R[-1]C[-3],2,FALSE)"
    Range("I11").Select
    ActiveCell.FormulaR1C1 = "=RC[-3]*RC[-1]"
    Range("I12").Select
End Sub

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Sub LABOUR()
Main ("Labour")
End Sub

Sub PLANT()
Main ("Plant")
End Sub

Sub Materials()
Main ("Materials")
End Sub
Sub Other()
Main ("Other")
End Sub
Sub OHP()
Main ("OH&P")
End Sub
Sub Main(T As String)

 R = Cells.Find(What:=T, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Row
        
    Rows(R + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows(R).Copy
    Rows(R + 1).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False

    With Range("A" & R + 1 & ":E" & R + 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=RATES!$A$2:$A$6"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

    Cells(R, 7).Value = "HRS"
    Cells(R, 8).FormulaR1C1 = "=VLOOKUP(RC[-7],RATES!R[-7]C[-7]:R[-3]C[-6],2,FALSE)"
    Cells(R, 9).FormulaR1C1 = "=RC[-3]*RC[-1]"
End Sub