Naming a range in VBA

  • Thread starter Thread starter cornishbloke
  • Start date Start date
C

cornishbloke

I am trying to write the VB code that will name a range (on a sheet
called 'Office Staff') according to the value in cell E4 (on a sheet
called 'Main Menu')

all I have of that row of code so far is...

Sheets("Office Staff").Range("B2:M15").Name =

I have already defined the E4 cell for other functions as:

Dim Oname As String
Oname = ActiveSheet.Range("E4").Value

Can I use this and refer to Oname value somehow???
 
What is contained in E4. If it is a string like B2:M15 you could do

With Sheets("Office Staff")
.Range(.Range("E4").Value).Name = "ABCD"
End With

or

Dim Oname As String
Oname = ActiveSheet.Range("E4").Value

With Sheets("Office Staff")
.Range(Oname).Name = "ABCD"
End With
 
I can't get either of those suggestions to work :(

cell E4 on the 'Main Menu' sheet will contain the full name of a ne
member of staff. When the macro below is triggered I already have th
name of the individual and their annual leave entitlement copied fro
the main menu and pasted into a blank table on the 'Office Staff
sheet. If the staff member being added is called "Bob Smith", I wan
the range covered by the table created for them (B2:M15) to be name
"Bob Smith".

Sub Add_Office()
'
'
Application.ScreenUpdating = False
Dim Oname As String
Dim Ohol As String
Oname = ActiveSheet.Range("E4").Value
Ohol = ActiveSheet.Range("G6").Value
If Oname = "" Then
MsgBox "Please enter Name", Buttons:=16
ElseIf Ohol = "" Then
MsgBox "Please enter Annual Leave Entitlement", Buttons:=16
Else
Sheets("Record Templates").Range("B2:M15").Copy
Sheets("Office Staff").Range("B2").Insert Shift:=xlDown
Sheets("Main Menu").Range("E4").Copy
Sheets("Office Staff").Range("B2").PasteSpecia
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Main Menu").Range("G6").Copy
Sheets("Office Staff").Range("D3").PasteSpecia
Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Main Menu").Range("E4:G4").ClearContents
Range("G6").ClearContents
Range("E4:G4").Select
End If
End Su
 
Back
Top