Using VBA to set Name in code

[CavanHomewoodInstall]

Hello everyone,

I want VBA to look up cell A46 and set the name. This is the code I currently have.

Sub Rectangle2_Click()

ActiveSheet.Copy after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = GetName
End Sub

Private Function GetName() As String
Dim x As Long, n As String
n = "JJJJ "
If SheetExists Then
Do
x = x + 1
If Not SheetExists(n & x) Then Exit Do
Loop
n = n & x
End If
GetName = n
End Function

Private Function SheetExists(aName As String) As Boolean
On Error Resume Next
Dim sh As Worksheet: Set sh = Sheets(aName)
If Err = 0 Then SheetExists = True Else SheetExists = False
End Function

 

[Fluff]

Hi & welcome to MrExcel.
What exactly is your question?

 

[CavanHomewoodInstall]

See a screenshot attached here.

 

[CavanHomewoodInstall]

Hi & welcome to MrExcel.
What exactly is your question?

I want the N = to look at cell A46 to get this information how do I do that

 

[Fluff]

Use

n = Range("A46").Value

 

[CavanHomewoodInstall]

Use

n = Range("A46").Value

Hi Fluff, is there a way I can create a gap between the +1 that is happening within my VBA code in a ideal world it would look like A46 - 1, A46 - 2 ect

 

[Fluff]

Yup you can use

n = Range("A46").Value & " - "