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

 

[StephenCrump]

I've posted the entire code for this macro

Sorry, my mistake. I thought this indicated missing code, but you were highlighting a relevant code line:

    ''''''''
    ''''''''
    ''''''''
    ''''''''

I have tested your code using some dummy .xls data files, and it works as I expected.

IF it's this code that's creating the sheet name copies, e.g. "Summary (2)" or "Annual Reconciliation Summary (3)" then that will be due to the ws.Copy code line.

If ws.Name = "Summary" Or ws.Name = "Annual Reconciliation Summary" Then
    ws.Copy ....
     '.....
     '.....
     '.....
End If

If so, I can see only three possibilities, depending on ws.Range("B11"):

1. It's a valid and non-duplicate sheet name, and the sheet will be renamed.
2. It's an invalid or duplicate sheet name, and the code will throw a run-time error.
3. It contains a value equal to the newly generated sheet name, e.g. Summary (2) or Annual Reconciliation Summary (3), so that the copied sheet is "renamed" with the same name.

The way I'd test this is to:
1. Check that I'm starting with a workbook that doesn't contain any of these sheet name copies.
2. Put in breakpoint(s) inside this If/End If code block, and step through the code to test what's happening.

Alternatively, if you have several workbooks to open and you want to let the code run unhindered, add this code snippet (in bold):

For Each ws In sWb.Worksheets
    If ws.Name = "Summary" Or ws.Name = "Annual Reconciliation Summary" Then
        ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        
        '....
        
        SheetName = ws.Range("B11").Value
        
        '....
      
        ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count).Name = SheetName
    End If
    Dim b As Boolean
    On Error Resume Next
    b = Len(ThisWorkbook.Worksheets("Summary").Name)
    b = Len(ThisWorkbook.Worksheets("Annual Reconciliation Summary").Name)
    On Error GoTo 0
    If b Then Stop  'Check what's happening
Next ws

This assumes you don't have "Summary" or "Annual Reconciliation Summary" worksheets in ThisWorkbook, before you run the code. If you do have these worksheets, just change to

    b = Len(ThisWorkbook.Worksheets("Summary (2)").Name)
    b = Len(ThisWorkbook.Worksheets("Annual Reconciliation Summary (2)").Name)

to trap the first sheet copy that doesn't get renamed properly.

 

[rachel06]

Appreciate all of the help Thank you!!!

 

[rachel06]

And this sounds really stupid, but I swear it's true.....I had it working yesterday and saved it. When I looked at it today, just do a formatting tweak (literally changing the color of some font), it's back to not renaming the sheet based on the cell value it's supposed to. It literally makes no sense. I might just have to add something at the end to loop through all the worksheets and rename.

 

[rachel06]

Okay, so I figured out if I have my "Headers" sheet hidden, it doesn't rename my files. If I don't have my "Headers" she hidden, it DOES rename my files. What a bizarre situation.

 

[StephenCrump]

Okay, so I figured out if I have my "Headers" sheet hidden, it doesn't rename my files.

Hmm, odd.

I've been assuming that the only issue is the failure to rename the worksheets, and that everyone else works as intended.

So, to be clear, if you run the code:

1. From some starting point, with "Headers" hidden, and then
2. From the same starting point, with "Headers" not hidden

do you end up with exactly the same workbook, with all the correct sheets added, with the only change being that in 1. the added worksheets are not renamed?

And just for the avoidance of any doubt, what is the ActiveSheet:

a) When you hide "Headers"
b) When you don't hide "Headers".

If these are different, is it perhaps the choice of ActiveSheet, rather than the hiding of "headers", that is causing the problem?

And do you have any other code that might be interfering?

I can only suggest again that you put in breakpoints, and step through your code line by line or chunk by chunk, to see what's actually happening (or not happening).

If all else fails, can you replicate the problem with a stripped down version of your workbook - removing data/calculations from the worksheets? If you can upload this to a drop box and post the link, we could take a look.

 

[rachel06]

Hi!

I found a new caveat with this. This workbook starts off with four sheets. If "Headers" is in the 4th position of those sheets and hidden, I end up with the correct sheets added and not renamed.

If I have "Headers" either not hidden or in the 1st, 2nd, or 3rd sheet position of those sheets, it runs as expected, with the sheets added and renamed.

I don't think any other code is interfering. It's very weird, but at least I know how to mitigate it now