sheet name in variable in VBA

[samirvshah]

I am trying to give my Sheet a name that is equal to the name or number I enter in a particular cell say for example I entered a number “700” in cell “G7” now my sheet name will also be “700” after that I try to copy that entire sheet after the current sheet and change the value in “G7” by entering a formula “Value of the same cell in previous sheet +1” so in the new sheet the value will be “701” now this new sheet will also change its name to the current new value I got



Can any one help me how to get the sheet name in variable in VBA so I can use it in my formula as I want to copy and add the sheets and change the name as mentioned above

 

[knight00779]

Hi,

I was able to get your first part of your Query/Case (i.e assigning the Name of the Sheet as entered in G7 cell. below is the way.

- Assumption 1: Your current Sheet Name (i.e visible Name) is Sheet2
- below VBA event captured and if the Target cell change is G7 then update the Sheet name.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$7" Then
Worksheets("Sheet2").Name = Range("G7").Value
End If
End Sub

Hope this helps

Rgrds