Hi,

I would like to optimize and simplify the below code and thus improve my knowleges of Excel/VBA.
Maybe there is an easiest way than doing a loop every time and switching excel windows all the time ?

Many thanks in advance for your help,

Greg

Here is the code :
'

VarUnrealizedPath = "C:\Documents and Settings\Gregory\Bureau\MacrosBK\"
VarFileName = (ThisWorkbook.Name)

Sheets("DATAS").Select

VarPreviousDate = Range("B2").Value
VarCurrentDate = Range("B1").Value

' **** Start of the Loop. The sheet FUND LIST contains all my funds. XYZ in A1 ABCD in A2 etc.

Sheets("FUND LIST").Select
Range("A2").Select
Do Until ActiveCell.Value = ""
VarFund = ActiveCell.Value


' **** Check id the file with my fund name exists

If Dir(VarUnrealizedPath & "AG-OSImport" & VarFund & "_" & VarPreviousDate & ".xls") <> "" Then
Workbooks.Open Filename:=VarUnrealizedPath & "AG-OSImport" & VarFund & "_" & VarPreviousDate & ".xls"

' **** If the file exists; the file is opened and I locate the cell that I want using a loop

Sheets("Navig").Select
Range("AC65000").Select
Do Until ActiveCell.Value <> ""
ActiveCell.Offset(-1, 0).Select
Loop
VarPDUnreal = ActiveCell.Value
ActiveWindow.Close (True)

' **** I do a loop to find the cell in my original file to paste the value

Windows("Update Manual AG.xls").Activate
Sheets("DATAS").Select
Range("A5").Select
Do Until ActiveCell.Value = VarFund
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(5, 2).Select
ActiveCell.Value = VarPDUnreal


' **** I am opening an other file, same process than beofre to locate,copy and paste

Windows("Update Manual AG.xls").Activate



Workbooks.Open Filename:= _
VarUnrealizedPath & "AG-OSImport" & VarFund & "_" & VarCurrentDate & ".xls"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst

Sheets("Setup").Select
Application.Run "'AG-OSImport" & VarFund & "_" & VarCurrentDate & ".xls'!Clear_Results"
Application.Run "'AG-OSImport" & VarFund & "_" & VarCurrentDate & ".xls'!OpenNavig"

Sheets("Navig").Select
Range("AC150").Select
Do Until ActiveCell.Value <> ""
ActiveCell.Offset(-1, 0).Select
Loop
VarCDUnreal = ActiveCell.Value

ActiveWindow.Close (True)

Windows("Update Manual AG.xls").Activate
Sheets("DATAS").Select
Range("A5").Select

Do Until ActiveCell.Value = VarFund
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(5, 3).Select
ActiveCell.Value = VarCDUnreal

' **** If the file does not exist ...

Else
MsgBox "Fund " & VarFund & " does not exist"

End If

' **** Go to the next cell in the FUND LIST and start again all the process

Sheets("FUND LIST").Select
ActiveCell.Offset(1, 0).Select

Loop