natphonics
New Member
- Joined
- Aug 6, 2007
- Messages
- 11
HI the following is a section of macro I've written to take data from a workbook and put it into another sheet and then run formula's against the data. It worked fine when I only wanted the data to come from one workbook. Now i Want it to loop through several workbooks in the fille and do the samething.
This worked fine wen it was always just one workbook
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",'[Budget model OFD 25-06-07.xls]Salary
Budget'!R1C2)"
Now I need it to go through several different workbooks and pull data from the sheet called Salary budget in each. The Budget model OFD 25-06-07.xls now needs to change depending on wich file the macro is being run in.
This what i tried:
Dim model As String
Dim FTEdb As String
'Application.ScreenUpdating = False
model = ActiveWorkbook.Name
Workbooks.Add
ActiveWorkbook.SaveAs ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Data " & model)
FTEdb = ActiveWorkbook.Name
Workbooks.Open ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Database Temp.xls")
Cells.Select
Range("A1").Activate
Selection.Copy
Windows(FTEdb).Activate
ActiveSheet.Paste
Windows(model).Activate
'Application.ScreenUpdating = True
UserForm1.Show
'Application.ScreenUpdating = False
Sheets("Salary Budget").Activate
Range("A7:A36").Select
Selection.Copy
Windows(FTEdb).Activate
Range("b4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""", model &'Salary Budget'!R1C2)"
Range("C4").Select
Selection.Copy
Range("C5:C40").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Any Help would be greatly apreciated.
This worked fine wen it was always just one workbook
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""",'[Budget model OFD 25-06-07.xls]Salary
Budget'!R1C2)"
Now I need it to go through several different workbooks and pull data from the sheet called Salary budget in each. The Budget model OFD 25-06-07.xls now needs to change depending on wich file the macro is being run in.
This what i tried:
Dim model As String
Dim FTEdb As String
'Application.ScreenUpdating = False
model = ActiveWorkbook.Name
Workbooks.Add
ActiveWorkbook.SaveAs ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Data " & model)
FTEdb = ActiveWorkbook.Name
Workbooks.Open ("G:\DVC\DVC Finance Budget\Budget 2007-08\Budget Models\FTE Database\FTE Database Temp.xls")
Cells.Select
Range("A1").Activate
Selection.Copy
Windows(FTEdb).Activate
ActiveSheet.Paste
Windows(model).Activate
'Application.ScreenUpdating = True
UserForm1.Show
'Application.ScreenUpdating = False
Sheets("Salary Budget").Activate
Range("A7:A36").Select
Selection.Copy
Windows(FTEdb).Activate
Range("b4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C4").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]="""","""", model &'Salary Budget'!R1C2)"
Range("C4").Select
Selection.Copy
Range("C5:C40").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Any Help would be greatly apreciated.