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

"(E-Mail Removed)" 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 :-)
>
>