Changing Named Ranges

[willheap]

Hello All,


I am still pretty fresh when it comes to VBA so the answer to this may be simpler than I have made it so far;


Sheet3 contains all the ranges that I want to add as Named Ranges. Row 1 contains the range name and everything below is the range i.e. A2:A2000 (All to be the same size range!). There are many columns in Sheet3, all requiring named ranges. The range names may already exist in the workbook but they are to be superseded with these new ranges in Sheet3.


So far, I have written the below code but it doesn't work. Actually, it worked the first time I ran it but, now it stops at the RefersTo range. I can't work out what is wrong with the range, if that is actually the problem...


Any help will be much appreciated.


Sub create_named_ranges()


Dim ws3 As Worksheet

Dim Cols As Integer

Dim Col As Integer

Dim Row As Integer

Dim n As String


'Stop the stuff

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.DisplayAlerts = False


'Give values

Set ws3 = ThisWorkbook.Worksheets(3)

Cols = ws3.UsedRange.Columns.Count

Row = ws3.UsedRange.Rows.Count


'Loop for each column

For Col = 1 To Cols


'Loop to delete existing Name first

For Each Name In Names

If Name = ws3.Cells(1, Col) Then

Name.Delete

End If

Next Name


'Add new Name and set range

wb.Names.Add Name:=ws3.Cells(1, Col), RefersTo:=ws3.Range(Cells(2, Col), Cells(Row, Col))


Next Col


'Start stuff back up

Application.CutCopyMode = False

Application.DisplayAlerts = True

Application.EnableEvents = True

Application.ScreenUpdating = True


End Sub

 

[willheap]

Just noticed that I had deleted the wb object parameters... Even after putting them back in (DIM wb As Workbook and Set wb = ThisWorkbook) it still doesn't work.

 

[bobhc]

Good afternoon William

If you select ALL your data coloums and then press Ctrl+Shift+F3 the create name from selection dialouge box will open,just make sure the creat name from values in the "Top row" is select press, OK and thats it. If you go to the name box at the top left,left of the fubnction box and click the small black down point you will see your ranges.

 

[willheap]

Thanks bobhc, that is useful to know.


Unfortunately, it doesn't solve my VBA problem though. I need this process automated as it must be updated every time the workbook is opened.

 

[NARAYANK991]

Hi William ,


I don't see wb defined anywhere in the code you have posted.


Narayan

 

[willheap]

Hello again NARAYANK991,


I deleted wb in an attempt to find my error and forgot to put it all back before posting the message.


It's on the line:

wb.Names.Add Name:=ws3.Cells(1, Col), RefersTo:=ws3.Range(Cells(2, Col), Cells(Row, Col))


My macro includes the following code to go with wb;

DIM wb As Workbook

Set wb = ThisWorkbook


The macro stops on the line starting with wb and comes up with;

Runtime error "1004"

Method 'Range' of object '_worksheet' failed


Besides this, I would expect the macro to delete the existing Named Range before reaching this point - but it doesn't.


It seems that I am missing something fundamental in this code but I just can't work out what it is.

 

[NARAYANK991]

Hi William ,


The following works on my computer :

[pre]

Sub create_named_ranges()

Dim ws3 As Worksheet
Dim Cols As Integer
Dim Col As Integer
Dim Row As Integer
Dim n As String
Dim wb As Workbook

'Stop the stuff
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

'Give values
Set wb = ThisWorkbook
Set ws3 = ThisWorkbook.Worksheets("Sheet4")
Cols = ws3.UsedRange.Columns.Count
Row = ws3.UsedRange.Rows.Count

'Loop for each column
For Col = 1 To Cols

'Loop to delete existing Name first
On Error Resume Next
For Each Nm In Names
If Nm = ws3.Cells(1, Col) Then
Nm.Delete
End If
Next Nm
On Error GoTo 0

'Add new Name and set range
wb.Names.Add Name:=ws3.Cells(1, Col), RefersTo:=ws3.Range(Cells(2, Col), Cells(Row, Col))

Next Col

'Start stuff back up
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

[/pre]
There's not much difference between what you have and what is posted here ; can you put this in and see if it works on your computer ?


Narayan

 

[willheap]

Thanks Narayan,


Unfortunately, it still doesn't work on my computer


Just to check that I haven't lost my marbles; did you run this in a module?


I have tested the part of the code that would delete the named ranges by changing the line below to text i.e.

'wb.Names.Add Name:=ws3.Cells(1, Col), RefersTo:=ws3.Range(Cells(2, Col), Cells(Row, Col))


I would expect the remaining code to delete all of the named ranges... but it doesn't. Have you checked to see if this code deletes any named ranges on your computer?


I am a bit confused because this code worked the first time I ran it, which is how I ended up with so many named ranges but it hasn't worked since.

 

[NARAYANK991]

Hi William ,


First I ran it from the sheet section ; it worked OK. Then I removed it from the sheet section and inserted it in a module ; it still worked OK.


Regarding the problem of deleting existing names , the problem was there ; resolved it by changing the section as below :

[pre]

For i = 1 To wb.Names.Count
If wb.Names.Item(i).Name = ws3.Cells(1, Col) Then
wb.Names.Item(i).Delete
End If
Next i

[/pre]
Narayan

 

[willheap]

Thanks a lot Narayan, that's fantastic!


It didn't work in the original module so I copied the exact same code to a sheet and it worked! The strange thing is, without changing any code at all it now works back in the module too?!?! I have no idea what was going on there but it works now, yipee!


Thanks Narayan


Will

 

[willheap]

Actually, strike that... it doesn't work in the module anymore! What on earth is going on there? Fortunately, it DOES work in the sheet.