Noorie,
Sure here is the code. It's quite complicated so unless you know the structure of the set up might not be easy to follow. But I've tried to breakout the relevant section for you under the heading RELEVANT CODE.
Elvis
Private Sub dataupdate()
Dim Lastrow As Long, Nextrow As Long
Dim File_name As String, Directory As String, Searchname As String, stpt As String
Dim lastquotedate As String, Lastdate As String, Source_Data_File As String, Col_Name As String
Dim c As Integer, lcol As Integer, vcol As Integer
Dim csv_file As Workbook, wb As Workbook
Dim rng1 As Range, rng2 As Range, rngstart As Range
Application.ScreenUpdating = False
'//Determine path name for finding & opening downloaded csv data files
'//which will have been automatically saved to the same file folder as the historical data workbook
File_name = ActiveWorkbook.FullName 'full name of current workbook
Searchname = ActiveWorkbook.Name
endpos = InStr(1, File_name, Searchname, 1)
endpos = endpos - 1
Directory = Left(File_name, endpos) 'drive and folders etc where the downloaded files are stored
'// find last column of data
lcol = Range("IV3").End(xlToLeft).Column
c = 3
Set rng1 = Range(Cells(3, 1), Cells(3, lcol))
'// Find Row No. for Present Value
PVrw = Application.WorksheetFunction.Match(" Present value = P", Range("A1:A" & Range("A65536").End(xlUp).Row), 0)
'// Loop through each col
Do While c < lcol
c = c + 2
Col_Name = rng1.Cells(1, c)
RELEVANT CODE
'//Determine last row of historical data file (this workbook sheet1 col D)
Lastrow = Cells((Cells(65536, c + 1).End(xlUp).Row), c).End(xlUp).Row
Nextrow = Lastrow + 1
'//Determine date of last historical data entry
Lastdate = Cells(Lastrow, 1).Value
Lastdate = Format(Val(Format(Cells(Lastrow, 1), "general number")), "d-mmm-yy")
'//Locate, open & activate downloaded csv data file
'Source_Data_File = Directory & "Update_" & Col_Name & ".csv" 'Full path and name of csv file
Source_Data_File = Directory & Col_Name & ".csv" 'Full path and name of csv file
'above line = old set up - searched for all files starting with "Update_"
Workbooks.Open (Source_Data_File)
'deletes headers - unneccessary rows
ActiveSheet.Rows("1:3").Delete
'//Format dates in Column A of csv file
'ActiveSheet.Columns("A").NumberFormat = "mmm-yyyy"
'sorts data
ActiveSheet.Columns("A:F").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlDescending
'//Determine date of most up-to-date closing price in csv file
lastquotedate = ActiveSheet.Cells(2, 1).Value
Latestdate = DateValue(lastquotedate)
'// Locate corresponding position in csv file of last price in historical data workbook
With Worksheets(1).Range("A1:A1000")
Set Rw = .Find(Lastdate, LookIn:=xlValues)
stpt = Rw.Address
End With
'// Activate csv file for copy
Workbooks(Col_Name & ".csv").Activate
'// Copy later data from International Index csv files
Range(stpt).Offset(-1, 0).Select
If ActiveCell.Row < 2 Then
MsgBox ("Data for Index " & Col_Name & " is up to date")
GoTo Close_out
Else
vcol = 2
Workbooks(ThisWorkbook.Name).Sheets("table").Activate
Cells(Nextrow, c).Select
While Range("A" & ActiveCell.Row).Value <= Latestdate
Nextrow = ActiveCell.Row
'ActiveCell.Formula = "=VLookup(A" & ActiveCell.Row & ",'Update_" & Col_Name & ".csv'!$A:$E,2,0)"
ActiveCell.Formula = "=VLookup(A" & ActiveCell.Row & ",'" & Col_Name & ".csv'!$A:$E,2,0)"
'above line tells it which column to bring back data from '$A:$E,2,0' tells it to bring back col 2
If IsError(ActiveCell) Then
ActiveCell = ""
Else
ActiveCell = ActiveCell.Value
Cells(PVrw, c) = ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Wend
End If
'// paint format down new data
Workbooks(ThisWorkbook.Name).Activate
Sheets("Table").Select
Cells(Lastrow, c).Select
Range(ActiveCell, ActiveCell).Select
Selection.AutoFill Destination:=Range(ActiveCell, Cells(Nextrow, c)), Type:=xlFillFormats