Move sheets to new workbook and rename

rachel06

Board Regular
Joined
Feb 3, 2016
Messages
173
Office Version
  1. 365
Platform
  1. Windows
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 :)

Code:
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hello @rachel06.
Try this option:
VBA Code:
                            ' 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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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!
 
Upvote 0
Move sheets to new workbook and rename_v1.png
Move sheets to new workbook and rename_v2.png
Move sheets to new workbook and rename_v3.png
Move sheets to new workbook and rename_result.png
 
Upvote 0
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.
 
Upvote 0
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:

VBA Code:
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,697
Messages
6,192,496
Members
453,727
Latest member
tuong_ng89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top