Copy Paste

Hi Rappj

You didn't attach a file but please do so in future- it nearly always helps us and you to get the right answer (when creating or editting your original question, you can use the Add Files... button to upload a representative Excel file -without any personal data- to show your existing data and any macros). 

Not sure what kind of formula you have but the VBA PasteSpecial method (for Range) can be used to copy formulae as follows:

The attached file has:

1. some repeats of Canadian province codes, in blue cells starting with "NL" at D5 (you can add many more)
2. green cells B5:C5 to the left of that, containing illustrative formulae only, as below
3. B5 adds of "0-" in front of of the code using

   ="0-" & D5

   e.g. "0-NL"
4. C5 adds "-9" after the code using

   ="=D5 & "-9"

   e.g. "NL-9"
5. empty cells below that in columns B:C

The orange button titled "Copy green cell formulae" is linked to this code in Module 1 (with comments for guidance):

Sub CopyCanPostCode()

    Dim LstRw As Long, StrtCll As Range

    ' emulate user code to find first Canadian code...
    Set StrtCll = Range("D5")
    '... and find last row
    LstRw = Cells(Rows.Count, StrtCll.Column).End(xlUp).Row

    ' pick the cell 2 to the left of the first Canadian cell
    With StrtCll.Offset(0, -2)
        ' copy that and the cell to the right of it
        .Resize(1, 2).Copy
        ' copy their formulae from the next row down to end of range
        .Offset(1, 0).Resize(LstRw - StrtCll.Row, 2).PasteSpecial xlPasteFormulas
    End With

    ' remove the dotted copy lines
    Application.CutCopyMode = False

End Sub

(where the early lines in bold replace whatever code you use to find the first-used Canadian province).

If you click that orange button, you'll find the formulae from B5 and C5 are copied to the cells below. There could be thousands of entries in D (or wherever).

To use it, you'll need use your code to determine StrtCll (=start cell)

Hope this fixes your problem. If so, please be sure to mark this Answer as Selected.