Help - applying macro to all files in folder
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 |
Help - applying macro to all files in folder
jrc
"=LOOKUP(""370302"",'[sFName]'!R2C2:R1308C2,'[sFName]'!R2C18:R1308C18)" Range("I9").Select ActiveCell.FormulaR1C1 = _ "=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. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
All times are GMT +1. The time now is 09:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com