Extracting data from various excel reports using VBA

[westconn1]

first you need to decide how to store the values, so a method to fill the array can be determined, then to loop through the array

for i = 0 to ubound(myarray)
   secondpart = myarray(i)
      ' you can change the variable name from specific (vauxhall) to a generic name [I]overall_status[/I], but it makes no difference
      vauxhall_overall_status = "=" & firstpart & Format(Target + 4, "yyyy-mm-dd") & secondpart
    Range("B2").offset(i,0).Value = vauxhall_overall_status
next

i didn't test this at all, the off set will make each row down the value of i, so the first will be on row 2+0, next row 2 +1, next row 2+2 etc

the array needs to contain a list of all the file names you want to process
you can hardcode an array like
myarray = Array("a", "S", "d", "r")
or read the content of a textfile or other methods, that can be changed without editing the vba code
or you can loop through all the files in a given folder

hth