Help - applying macro to all files in folder

  • Thread starter Thread starter jrc123
  • Start date Start date
J

jrc123

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
 
jrc

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

"=LOOKUP(""370302"",'[" & sFname & "]'!R2C2...

sFName is part of your string so Excel is putting it literally in the
formula. By concatenating sFName in the string, Excel will evaluate the
variable and put the actual name in the formula.
 
Back
Top