Move sheets to new workbook and rename

[rachel06]

Hi!

I was reviewing some of my macros today, and for some reason, this one isn't working as expected. Hoping to get some help here.

The line "SheetName = ws.Range("B11").Value is being skipped for some reason, and each ws comes in with their original name, with a (1), (2), etc appended to it

Any ideas why this step seems to be skipped?? I tried to make this part of the code stand out below

Sub aGatherSummaries()
  
    Dim sWb As Workbook
    Dim ws As Worksheet
    Dim FolderName As String, SheetName As String
    Dim FFolder As Object, FFile As Object

    With CreateObject("Scripting.FileSystemObject")
        FolderName = Trim(ActiveSheet.Range("B3").Value)
      
        'Test folder validity
        If Not .FolderExists(FolderName) Then
            MsgBox "The folder path in Cell B3 is invalid." & vbCr & vbCr & "'" & FolderName & "'", vbOKOnly Or vbExclamation, "Folder Path Error"
            Exit Sub
        End If

        Set FFolder = .GetFolder(FolderName)

        'Process excel files in that folder
        Application.ScreenUpdating = False
        For Each FFile In FFolder.Files
            'declare the file type of the worksheets to gather
            If InStr(1, FFile.Name, ".xls", vbTextCompare) > 0 Then
                If ThisWorkbook.Name <> FFile.Name Then
                    On Error Resume Next
                    Set sWb = Nothing
                    Set sWb = Workbooks.Open(FFile.Path)
                    On Error GoTo 0
                    If Not sWb Is Nothing Then
                        For Each ws In sWb.Worksheets
                            'declare sheet name
                            If ws.Name = "Summary" Or ws.Name = "Annual Reconciliation Summary" Then
                                ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
                              
                               ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
 
                                'Imported sheet name defined by cell B11
                                SheetName = ws.Range("B11").Value
                              
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                ''''''''
                                'Any sheets with the same name are overwritten
                                Application.DisplayAlerts = False
                                On Error Resume Next
                                ThisWorkbook.Worksheets(SheetName).Delete
                                On Error GoTo 0
                                Application.DisplayAlerts = True
                              
                                ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = SheetName
                            End If
                        Next ws
                        sWb.Close False
                    End If
                End If
            End If
        Next FFile
    End With
    End Sub

 

[MikeVol]

Hello @rachel06.
Try this option:

                            ' declare sheet name
                            If ws.Name = "Summary" Or ws.Name = "Annual Reconciliation Summary" Then
                                ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)

                                ' Checking if a value is present in B11
                                If IsError(ws.Range("B11").Value) Or IsEmpty(ws.Range("B11").Value) Then
                                    MsgBox "Error: empty or invalid cell B11 on the sheet " & ws.Name
                                    Exit Sub
                                End If

                                ' Checking if a sheet name is valid
                                SheetName = ws.Range("B11").Value
                                
                                ' Removing invalid characters
                                SheetName = Replace(SheetName, "/", "_")

                                ' Delete an existing sheet with the same name
                                Application.DisplayAlerts = False
                                On Error Resume Next
                                ThisWorkbook.Worksheets(SheetName).Delete
                                On Error GoTo 0
                                Application.DisplayAlerts = True

                                ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = SheetName
                            End If

If the problem persists after this, please let me know. Good luck.

 

[rachel06]

Hello! It's still not renaming the file based on that cell value It's just keeping the name of the sheet as is. No idea what's going on with it. Not sure if it's how I have things saved since I'm testing it? I've been using this for months and it's suddenly giving me issues.

 

[MikeVol]

It still does not rename the file based on the cell value. So it should not rename the file, the code does not have this functionality! The code saves the new inserted sheet with the name taken from cell B11 of the viewed files that it (the macro) opens.

Most likely, you found this macro code somewhere on the Internet and, not knowing its functionality, you are trying to get a different result from the macro. No, you will not be able to rename the file(s) with this code!

 

[MikeVol]

And you should have received a message that cell B11 is empty or some error (as a result of the formula(s) running). Or another error message. You are not telling us something!

 

[MikeVol]

 

[rachel06]

Hi. I misspoke in my previous post. It's not renaming the SHEET based on cell B11. Sorry I said file. It should be renaming the SHEET, but it's keeping the sheet name (Summary), then adding like Summary(1), Summary(2) etc.

I was using this just fine for a while and now it's suddenly not working. I'm just confused as to why and thought someone might see something in the code.

 

[StephenCrump]

The code you've posted should rename the sheets, but perhaps there's something going on in the intermediate code you've not posted?

I suggest you put breakpoints on these two lines:

SheetName = ws.Range("B11").Value
'....
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = SheetName

Are these lines being executed? What is the value of SheetName? Do you perhaps have an On Error Resume Next which might be masking the fact that the second line is erroring, e.g. due to an invalid sheet name?

 

[rachel06]

I've posted the entire code for this macro. There are other macros in the workbook, but those aren't at all used for this one.

What's weird is that exact code is working in one of my older versions of this file, but not in my most recent one. I might just have to work off of an old version. This one is just so weird.

 

[MikeVol]

Did you follow the advice given by StephenCrump in the post above? We can't see your data, so we're guessing. But you don't want to help with that. I'm out.