i think i know what you mean but could you break out the code as an example and for the Data Extract Tab my Unique identifier starts in cell C1 and were i have the Unique Identifier on the New Jersey File (using the vlookup) is starting in cell C1 as well. So for my Code for the New Jersey Tab i have as following:
Sub ComplianceTest()
'To create formulas automatically'
Dim LastRow As Long
With Sheets("New Jersey File")
LastRow = .Range("C" & Rows.Count).End(xlUp).Row
.Range("I2:I" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$F$4:$F$660,4,0)"
.Range("J2:J" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$H$4:$H$660,6,0)"
.Range("K2:K" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$I$4:$I$660,7,0)"
.Range("L2:L" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$J$4:$J$660,8,0)"
.Range("M2:M" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$K$4:$K$660,9,0)"
.Range("N2:N" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$L$4:$L$660,10,0)"
.Range("O2:O" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$M$4:$M$660,11,0)"
.Range("P2:P" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$N$4:$N$660,12,0)"
.Range("Q2:Q" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$O$4:$O$660,13,0)"
.Range("R2:R" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$P$4:$P$660,14,0)"
.Range("S2:S" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$Q$4:$Q$660,15,0)"
.Range("T2:T" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$R$4:$R$660,16,0)"
.Range("U2:U" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$S$4:$S$660,17,0)"
.Range("V2:V" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$T$4:$T$660,18,0)"
.Range("W2:W" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$U$4:$U$660,19,0)"
.Range("X2:X" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$V$4:$V$660,20,0)"
.Range("Y2:Y" & LastRow).Formula = "=VLOOKUP(C2,'Data Extract'!$C$4:$C$650:'Data Extract'!$W$4:$W$660,21,0)"
.Range("I2:Y" & LastRow).Value = .Range("I2:Y" & LastRow).Value
End With
Columns I through Y are pulling data from the Data Extract Tab using cell C2 (My unique identifier) as the lookup value.