Hi all,

Can anybody please help...

I have +100 workbooks that i need to change a named range in (all in a
folder). Is there a way to amend all of them via vba? the thought of
doing it all manually fills me with dread!


ie. Unhide worksheet 'Data', replace range of cells A1:A5 with A, B,
C, D, E and define/name this range as 'Option' and re-hide the sheet

Any help whatsoever would be greatly appreciated.

many thanks

P :-)

Something like this????

Sub changebooks()

MyPath = "c:\temp\"

First = True
Do
If First = True Then
Filename = Dir(MyPath & "*.xls")
First = False
Else
Filename = Dir()
End If

If Filename < "" Then

Workbooks.Open MyPath & Filename
'no need to unhide cells
With Sheets("data")
.Range("A1").Value = A
.Range("A2").Value = B
.Range("A3").Value = C
.Range("A4").Value = D
.Range("A5").Value = E
End With
ActiveWorkbook.Names.Add Name:="Option", _
RefersToR1C1:="=DATA!R1C1:R5C1"
Workbooks(Filename).Close

End If
Loop While Filename < ""

End Sub

" wrote:

Hi all,

Can anybody please help...

I have +100 workbooks that i need to change a named range in (all in a
folder). Is there a way to amend all of them via vba? the thought of
doing it all manually fills me with dread!


ie. Unhide worksheet 'Data', replace range of cells A1:A5 with A, B,
C, D, E and define/name this range as 'Option' and re-hide the sheet

Any help whatsoever would be greatly appreciated.

many thanks

P :-)