Can't rename cell using VBA

[Coda]

Hello everyone,

I'm trying to make a spreadsheet with a macro that asks the user for the Case ID (case_id), and creates a new sheet which is copied from a template (wsTemp). I then want to rename the new sheet with the case ID that the user provided, and also enter the case ID in the cell A1 of the new sheet.

That's where the problem is. For some reason, Excel creates a sheet and calls it "Template (2)" instead using the case ID. It also does not change the value of cell A1. Any ideas?

Sub new_case_macro ()

Dim wsTemp As Worksheet

Dim case_id As String

case_id InputBox ("Enter Case ID: ")

With ThisWorkbook

Set wsTemp Sheets ("Template") wsTemp.Copy Before:-.Sheets (.Sheets.Count)

ActiveSheet.Range ("Al").Value -case_id

ActiveSheet.Name = case_id

End With

End Sub

 

[RoryA]

I assume that is not the actual code since you are missing a lot of = signs in it.

 

[Coda]

I assume that is not the actual code since you are missing a lot of = signs in it.

Oh, I'm sorry.. I had google lens copy the code from a picture and didn't notice how flawed it was. Here's the code again:

Sub new_case_macro ()

Dim wsTemp As Worksheet
Dim case_id As String

case_id = InputBox ("Enter Case ID: ")

With ThisWorkbook

Set wsTemp = .Sheets ("Template")
wsTemp.Copy Before:=.Sheets(.Sheets.Count) 
ActiveSheet.Range("A1").Value = case_id ActiveSheet.Name = case id

End With
End Sub

 

[RoryA]

If you are not getting an error then there must be a sheet that is getting renamed and has A1 altered, even if it's not the one you want. I'd suggest changing the last lines to:

with .Sheets(.Sheets.Count - 1)
    .Range("A1").Value = case_id
   .Name = case_id
End With

rather than:

ActiveSheet.Range("A1").Value = case_id
ActiveSheet.Name = case_id

 

[Coda]

Hello again!

I tried your proposed solution:

Sub new_case_macro ()

Dim wsTemp As Worksheet
Dim case_id As String

case_id = InputBox ("Enter Case ID: ")

With ThisWorkbook

Set wsTemp = .Sheets ("Template")
wsTemp.Copy Before:=.Sheets(.Sheets.Count) 

with .Sheets(.Sheets.Count - 1)

   .Range("A1").Value = case_id
   .Name = case_id

End With

End With
End Sub

Unfortunately I am still getting the same result

 

[RoryA]

Do you have any other event code in the workbook?

 

[Coda]

Ok, Update. I closed the file and opened it again, and it just works... Thank you Rory for the solution!