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 |
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 |