Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro creating folder and files automatically, without permission. | Excel Discussion (Misc queries) | |||
Macro to create a folder and copy files | New Users to Excel | |||
Path and Number of files in a folder. - Pass to Macro. | Excel Discussion (Misc queries) | |||
Macro to copy range from Excel files in folder | Excel Discussion (Misc queries) | |||
Macro to Print All Files in a Folder | Excel Programming |