Hi,
I need to insert ;
Formulae1 at cell CW62
Formulae2 at cell AC5
Formulae3 at cell DC12
Formulae4 at cell CG54
in multiple excel files at Sheet1
Thanks
Hi,
I need to insert ;
Formulae1 at cell CW62
Formulae2 at cell AC5
Formulae3 at cell DC12
Formulae4 at cell CG54
in multiple excel files at Sheet1
Thanks
Select all the sheets to group the sheets..
Then in one sheet type the formulas. The formulas will be in selected sheets.
Select all the sheets to group the sheets..
Then in one sheet type the formulas. The formulas will be in selected sheets.
Understood.
But the sheets are in different workbooks.
You would need to open each workbook . This code asks the user to select a folder then adds a formula to A1. Just add a line for each formula based on this line.
Sub LoopFiles()
Dim oWb As Workbook
Dim sFldr As String, sFilName As String
Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
If fDialog.Show = -1 Then
sFldr = fDialog.SelectedItems(1)
Else: MsgBox "User cancelled selection"
Exit Sub
End If
sFilName = Dir(sFldr & "\*.xls*")
Do While sFilName > ""
Set oWb = Workbooks.Open(sFldr & "\" & sFilName)
oWb.Sheets(1).Range("A1").Formula = "=IF(A1<A2,""True"",""False"")" ''///formula here
''///add other formulas in new lines
oWb.Close SaveChanges:=False ''///Close opened worbook w/o saving, change as needed
sFilName = Dir()
Loop
MsgBox "All files updated", vbInformation, "Success"
End Sub
Display More
You would need to open each workbook . This code asks the user to select a folder then adds a formula to A1. Just add a line for each formula based on this line.
CodeDisplay MoreSub LoopFiles() Dim oWb As Workbook Dim sFldr As String, sFilName As String Dim fDialog As Object Set fDialog = Application.FileDialog(msoFileDialogFolderPicker) If fDialog.Show = -1 Then sFldr = fDialog.SelectedItems(1) Else: MsgBox "User cancelled selection" Exit Sub End If sFilName = Dir(sFldr & "\*.xls*") Do While sFilName > "" Set oWb = Workbooks.Open(sFldr & "\" & sFilName) oWb.Sheets(1).Range("A1").Formula = "=IF(A1<A2,""True"",""False"")" ''///formula here ''///add other formulas in new lines oWb.Close SaveChanges:=False ''///Close opened worbook w/o saving, change as needed sFilName = Dir() Loop MsgBox "All files updated", vbInformation, "Success" End Sub
Excellent.
Its working absolutely fine.
You are great RoyUK
Pleased to help.
Post back if you need further help.
Visit my web site, http://www.excel-it.com, for more examples and some helpful articles.
Hi,
I need to insert two formula's, in Sheet GOSTOP
Following formula at cell C2 and then copied it down to cell C102.
=IF(AND(C3<0,C2>0),"GO"," ")
so that formula at C102 shows =IF(AND(C103<0,C102>0),"GO"," ")
And following formula at E2 and then copied it down to cell E102.
=IF(AND(E3>0,E2<0),"STOP"," ")
so that formula at E102 shows =IF(AND(E103>0,E102<0),"STOP"," ")
I need this to be done across multiple files in a given folder.
Any Help
Please
Different question requires a new post.
I have already created new thread for this.
Inserting Formula in multiple files - Excel VBA / Macros - OzGrid Free Excel/VBA Help Forum
Don’t have an account yet? Register yourself now and be a part of our community!