Check to see if sheet exists and rename it

[gingerbreadgrl]

Hi All,

I would like to run a macro to check to see if a sheet name exists, if it does exist, I would like to rename that sheet to be the contents of whatever is in cell B1 of that sheet. So far I have not had any luck with the using the following code, seems like it would be simple enough but I'm having trouble. Any thoughts would be much appreciated! Thanks, Gingerbreadgrl

Sub SummaryReportMacro()


Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
         If ws.Name <> "Record 2" Then
            ws.Name = ws.Range("B1").Value
        End If
    Next ws


End Sub

I've also tried:

Sub SummaryReportMacro()

Dim sht As Worksheet
For Each sht In Sheets
If sht.Name = "Record 2" Then
Sheets("Record 2") = Range("B1").Value
End If

End Sub

This didn't work either:

Sub SummaryReportMacro()

'If Sheets("Record 2").Name Then Sheets("Record 2").Name = Range(B1).Value

End Sub

 

[Fluff]

How about

Sub gingerbreadgirl()
   Dim ShtName As String
   
   ShtName = "Record 2"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
      End With
   End If
End Sub

 

[gingerbreadgrl]

Hi Fluff,

This is fantastic! I was really spinning my wheels with this. Can you just give me a very brief description of how it works, just so I understand a little bit better? Especially the if Evaluate part, I've never seen anything like that before and I'm trying to learn as much as I can.

Best,
Gingerbreadgrl

 

[Fluff]

The Evaluate is calculating a worksheet formula, so if you put
=ISREF('Record 2'!A1)
into a cell it returns TRUE (assuming you have a sheet called Record 2)
So it's just checking if the sheet exists & if it does it will then rename it.

HTH

 

[gingerbreadgrl]

Thanks Fluff! That makes a lot of sense now. I know that this is a slightly different, but now that I have the sheet name renamed to the value in B1 can I reference that name to save it? The sheet number that was renamed is sheet number 11. I just need to know how to reference the sheet name in the file pathway. I'm so close in making this code work:

ShtName = "Record 2"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
      End With
      
      Sheets(11).Copy
      With activeworkook
      .SaveAs Filename:="G:\My Drive\Gingerbreadgrl\[COLOR=#ff0000]Sheets(11).Name[/COLOR]", FileFormat:=xlOpenXMLWorkbook
      .Close savechanges:=False
      End With
      
   End If

 

[Fluff]

Try

   Dim ShtName As String
   
   ShtName = "Record 2"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
         .Copy
      End With
      With ActiveWorkbook
         .SaveAs "G:\My Drive\Gingerbreadgrl\" & Sheets(1).Name, 52
         .Close savechanges:=False
      End With
   End If

 

[gingerbreadgrl]

Hi Fluff,

This works great for the first sheet that is renamed, but I have matching code below and it errors out on the second file pathway.

   ShtName = "Record 2"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
         .Copy
      End With
      With ActiveWorkbook
         .SaveAs "G:\My Drive\Gingerbreadgrl\" & Sheets(1).Name, 52
         .Close savechanges:=False
      End With
   End If

   ShtName = "Record 3"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
         .Copy
      End With
      With ActiveWorkbook
[COLOR=#ff0000]         .SaveAs "G:\My Drive\Gingerbreadgrl\" & Sheets(1).Name, 52[/COLOR]
         .Close savechanges:=False
      End With
   End If

 

[gingerbreadgrl]

Hmm I just tried it again and it worked just find I realized that I had an space between the end with and the with, would that have caused the error?

ShtName = "Record 3"
   If Evaluate("Isref('" & ShtName & "'!A1)") Then
      With Sheets(ShtName)
         .Name = .Range("B1").Value
         .Copy
      [COLOR=#ff0000]End With[/COLOR]


[COLOR=#ff0000]      With ActiveWorkbook[/COLOR]
         .SaveAs "G:\My Drive\Gingerbreadgrl\" & Sheets(1).Name, 52
         .Close savechanges:=False
      End With
   End If

 

[Fluff]

Having extra blank lines shouldn't cause any problems. How many of these do you have to do?

 

[gingerbreadgrl]

Nope it didn't just tested with and without the space, must have been a fluke! Anywho, what does the 52 mean after the Sheets(1).Name?

Thanks so much!! I really appreciate your expertise!

Best,
Gingerbreadgrl