CODEWITHSUNDEEP
vbaexcelexcel-2013
DataNinja
DataNinja

Reputation: 67

VBA - Variable Not updating after a second run of the macro

Okay, so I have a spreadsheet in which I am trying to copy as a new sheet then re-name as a value of a cell on the original spreadsheet. When I run the macro the first time, it works fine. Ever subsequent time, however, it gives me the "Name is already taken" error and tries to pass the value of the original variable (x) again instead of taking the new value in cell C2.

I am self-taught and still fairly new at this, so any help would be greatly appreciated.I feel like the error might be coming in my range reference, as in there is a different way to declare the variable (x) to that cells' value that I might be missing. 

 Sub freezesheet()
    Dim x As String

    Sheets("Sheet1").Activate
    x = Range("C2:C2").Value
    Debug.Print (x)

    Sheets("Sheet1").Copy after:=Sheets(3)
    Sheets("Sheet1 (2)").Name = x
    x = ""

    Debug.Print (x)

    Cells.Select
    selection.Copy
    selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

    End Sub

Upvotes: 1

Views: 2414

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

There cannot be two sheets with the same name. So when you are running the code second time, the new sheet added will be renamed on the basis of cell C2 from Sheet1.

Also you can shorten your code like this...

Sub freezesheet()
   Dim x As String

   Sheets("Sheet1").Activate
   x = Range("C2").Value

   Sheets("Sheet1").Copy after:=Sheets(3)
   ActiveSheet.Name = x
   ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub

The above code will run without an issue if you change the C2 value each time before running the code next time.

Upvotes: 1

Related Questions