VBA Alter Formula Depending on ActiveSheet Criteria

[Ottsel]

I have a sheet where I need to extract a small array of data and using reference formulas saves a lot of time, but I feel there is a way to structure it, so it can a bit more automated.
Row 1 is my header, and column A contains data that'll help fill out the other cells

Columns B:F will contain the formula and depending on what's if column A contains any values it'll need to be entered.

The beginning of the reference is always:
='G:\JC\
After which the content in 'G1' \ 'A1' \

Then I encounter one that usually always stops me from being able to complete this, which is the first half is always the same followed by the file type, but the number will change, which depends on what is in column A

CMT Column A
# Column B
REF Column C
ID Column D
LOT COLUM E
NAME Column F

the endings of the formula change depending on the column they're in, but will be structured the same after all the references have been met.
After being completed will look like this:

B2

='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$AN$4

C2

='G:\JC\KBH\CMT\[Lot84.xls]LCV'!$M$3

D2

='G:\JC\KBH\CMT\[Lot84.xls]Plan'!$B$11

E2

='G:\JC\KBH\CMT\[Lot84.xls]Fm'!$I$8

F2

='G:\JC\KBH\CMT\[Lot84.xls]Jcm'!$N$5

Column A could contain multiple inputs, but normally ranges anywhere from 1-10 entries.

Any help, ideas or code examples would be highly appreciated.

 

[Ottsel]

Well, I managed to get it to work, but had to use an IF string, which is.. err... I know it could be better with a loop, but i'm still not the best with those. If anyone would like to convert this to a loop I'd appreciate it, so I could take notes from it.

Option Explicit

Sub ImportData()

    Dim FormulaStart As String
    Dim FormulaHook As String
    Dim PC As String
    Dim Address As String
    Dim Plan As String
    Dim Elev As String
    Dim Erifco As String
   
    FormulaStart = "='G:\JOBCARDS\" & ActiveSheet.Range("G1").Value & "\" & _
        ActiveSheet.Range("A1").Value & "\[Lot"
    FormulaHook = ".xls]"
    PC = "Plan'!$AN$4"
    Address = "LCV'!$M$3"
    Plan = "Plan'!$B$11"
    Elev = "Foreman'!$I$8"
    Erifco = "JobCard'!$N$5"
    'Prevent from Forumlas filling all the way down
    Application.AutoCorrect.AutoFillFormulasInLists = False

    'Row 2
    If ActiveSheet.Range("A2") > "" Then
        Range("B2").Value = FormulaStart & Range("A2").Value & FormulaHook & PC
        Range("C2").Value = FormulaStart & Range("A2").Value & FormulaHook & Address
        Range("D2").Value = FormulaStart & Range("A2").Value & FormulaHook & Plan
        Range("E2").Value = FormulaStart & Range("A2").Value & FormulaHook & Elev
        Range("F2").Value = FormulaStart & Range("A2").Value & FormulaHook & Erifco
    End If
   
    'Row 3
    If ActiveSheet.Range("A3") > "" Then
        Range("B3").Value = FormulaStart & Range("A3").Value & FormulaHook & PC
        Range("C3").Value = FormulaStart & Range("A3").Value & FormulaHook & Address
        Range("D3").Value = FormulaStart & Range("A3").Value & FormulaHook & Plan
        Range("E3").Value = FormulaStart & Range("A3").Value & FormulaHook & Elev
        Range("F3").Value = FormulaStart & Range("A3").Value & FormulaHook & Erifco
    End If

    'Row 4
    If ActiveSheet.Range("A4") > "" Then
        Range("B4").Value = FormulaStart & Range("A4").Value & FormulaHook & PC
        Range("C4").Value = FormulaStart & Range("A4").Value & FormulaHook & Address
        Range("D4").Value = FormulaStart & Range("A4").Value & FormulaHook & Plan
        Range("E4").Value = FormulaStart & Range("A4").Value & FormulaHook & Elev
        Range("F4").Value = FormulaStart & Range("A4").Value & FormulaHook & Erifco
    End If

    'Row 5
    If ActiveSheet.Range("A5") > "" Then
        Range("B5").Value = FormulaStart & Range("A5").Value & FormulaHook & PC
        Range("C5").Value = FormulaStart & Range("A5").Value & FormulaHook & Address
        Range("D5").Value = FormulaStart & Range("A5").Value & FormulaHook & Plan
        Range("E5").Value = FormulaStart & Range("A5").Value & FormulaHook & Elev
        Range("F5").Value = FormulaStart & Range("A5").Value & FormulaHook & Erifco
    End If
   
    'Row 6
    If ActiveSheet.Range("A6") > "" Then
        Range("B6").Value = FormulaStart & Range("A6").Value & FormulaHook & PC
        Range("C6").Value = FormulaStart & Range("A6").Value & FormulaHook & Address
        Range("D6").Value = FormulaStart & Range("A6").Value & FormulaHook & Plan
        Range("E6").Value = FormulaStart & Range("A6").Value & FormulaHook & Elev
        Range("F6").Value = FormulaStart & Range("A6").Value & FormulaHook & Erifco
    End If
   
    'Row 7
    If ActiveSheet.Range("A7") > "" Then
        Range("B7").Value = FormulaStart & Range("A7").Value & FormulaHook & PC
        Range("C7").Value = FormulaStart & Range("A7").Value & FormulaHook & Address
        Range("D7").Value = FormulaStart & Range("A7").Value & FormulaHook & Plan
        Range("E7").Value = FormulaStart & Range("A7").Value & FormulaHook & Elev
        Range("F7").Value = FormulaStart & Range("A7").Value & FormulaHook & Erifco
    End If
   
    'Row 8
    If ActiveSheet.Range("A8") > "" Then
        Range("B8").Value = FormulaStart & Range("A8").Value & FormulaHook & PC
        Range("C8").Value = FormulaStart & Range("A8").Value & FormulaHook & Address
        Range("D8").Value = FormulaStart & Range("A8").Value & FormulaHook & Plan
        Range("E8").Value = FormulaStart & Range("A8").Value & FormulaHook & Elev
        Range("F8").Value = FormulaStart & Range("A8").Value & FormulaHook & Erifco
    End If
   
    'Row 9
    If ActiveSheet.Range("A9") > "" Then
        Range("B9").Value = FormulaStart & Range("A9").Value & FormulaHook & PC
        Range("C9").Value = FormulaStart & Range("A9").Value & FormulaHook & Address
        Range("D9").Value = FormulaStart & Range("A9").Value & FormulaHook & Plan
        Range("E9").Value = FormulaStart & Range("A9").Value & FormulaHook & Elev
        Range("F9").Value = FormulaStart & Range("A9").Value & FormulaHook & Erifco
    End If
   
    'Row 10
    If ActiveSheet.Range("A10") > "" Then
        Range("B10").Value = FormulaStart & Range("A10").Value & FormulaHook & PC
        Range("C10").Value = FormulaStart & Range("A10").Value & FormulaHook & Address
        Range("D10").Value = FormulaStart & Range("A10").Value & FormulaHook & Plan
        Range("E10").Value = FormulaStart & Range("A10").Value & FormulaHook & Elev
        Range("F10").Value = FormulaStart & Range("A10").Value & FormulaHook & Erifco
    End If
   
    'Formula be-gone
    Range("A2:G10").Copy
    Range("A2:G10").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Activate
   
    're-enable this feature
    Application.AutoCorrect.AutoFillFormulasInLists = True
   
End Sub