Hi,

Can anyone help?

I have an adjustment sheet that needs populating using lookups fro
another spreadsheet. Then I need to save the changes to the adjustmen
spreadsheet with the filename of the other spreadsheet & "adjustmen
sheet".

However I have over 300 spreadsheets, all saved in the same folder
which have the data in the same row/columns etc that I also need to us
to populate the adjustment sheet. Saving each individually.

I'm a macro novice but I've tried the below, but I've encountere
problems as excel is not remembering the opened spreadsheet '[sFName]
and asking for update value. It's probably completely wrong. Any advic
is appreciated.

Dim sFName As String
sFName = Dir("F:\Reports\*.xls")
Do While Len(sFName) 0


Workbooks.Open (sFName)
Workbooks.Open("F:\Adjustment Sheet.xls")
Windows("Adjustment Sheet.xls").Activate
Range("A1").Select
ActiveCell.FormulaR1C1 = _
"='[sFName]'!R2C1"
Range("B9").Select
ActiveCell.FormulaR1C1 = _

"=LOOKUP(""370302"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C18:R1308C18)"
Range("I9").Select
ActiveCell.FormulaR1C1 = _

"=-LOOKUP(""370302"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C18:R1308C18)"
Range("B10").Select
ActiveCell.FormulaR1C1 = _

"=LOOKUP(""800201"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C18:R1308C18)"
Range("I10").Select
ActiveCell.FormulaR1C1 = _

"=-LOOKUP(""800201"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C18:R1308C18)"
Range("N9").Select
ActiveCell.FormulaR1C1 = _

"=LOOKUP(""370302"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C32:R1308C32)"
Range("U9").Select
ActiveCell.FormulaR1C1 = _

"=-LOOKUP(""370302"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C32:R1308C32)"
Range("N10").Select
ActiveCell.FormulaR1C1 = _

"=LOOKUP(""800201"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C32:R1308C32)"
' etc etc etc and many more adjustments
ActiveWorkbook.SaveAs Filename:="sFName&""adjustmen
sheet"".xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:=""
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close

Loop
End Su

--
jrc12
-----------------------------------------------------------------------
jrc123's Profile: http://www.excelforum.com/member.php...fo&userid=1612
View this thread: http://www.excelforum.com/showthread.php?threadid=27566