help with showing tabs not copied over

[oldmanwilly]

Hi

I have a macro that copies one workbooks back data sheets into my workbooks back data sheets with the same name. I wanted a macro to insert the sheets names that were not copied over (in error etc.) so that I could do it manually. I couldn't really get it to list the sheets that weren't copied over and I'm wondering if someone knows how to insert the name of the sheet onto a sheet in myworkbook that didn't get data copied over?

Heres my code, I basically colour the sheets a different colour then check the colour of the tab if it has changed or not if it has I know the sheet was copied successfully. If you can also improve my code that would be great I'm still learning.

Hope this all makes sense if not give me a question. Thanks for the help.

Sub openQDDATA()
    Dim opan As String
    Dim QDtabs As Variant
    
    Dim wb As Object
    Dim Sh As Variant
    Dim ws As Worksheet
    Dim myrng2 As Range
    ThisWorkbook.Activate
    Sheets("CHECK LIST").Visible = True
                                     Sheets("CHECK LIST").Select
                                     
                                    Range("c2:c2000").ClearContents
                                    
    'this array has all the back tab names of the other worksheet
    QDtabs = Array(Sheets("lo"), Sheets("ki"), Sheets("gj"), Sheets(""), Sheets("m"), _
    Sheets("n"), Sheets("b"), Sheets("v"), Sheets("x"), Sheets("z"), Sheets("a"), Sheets("q"), _
    Sheets("w"), Sheets("e"), Sheets("f"), Sheets("g"), Sheets("h"), _
    Sheets("frtg"), Sheets("gtrew"), Sheets("hjku"), Sheets("care"), Sheets("js"), Sheets("Myt"), _
    Sheets("we"), Sheets("hgfds"), Sheets("vfds"), Sheets("bg"), Sheets("fn"), _
    Sheets("de"), Sheets("fr"), Sheets("gr"), Sheets("kiu"), Sheets("HIP"), Sheets("loe"), _
    Sheets("lop"), Sheets("ki"), Sheets("po"), Sheets("plk"))
    
        'Makes all the qdtab worksheets in thisworkbook visible
        For Each Sh In QDtabs
            Sh.Visible = True
        Next
        
        'sets whatever file is selected as a variable opan
        opan = Application.GetOpenFilename
            'opens opan and puts it in the variable wb
            Set wb = Workbooks.Open(opan)
            
                'unprotects other workbook
                ActiveWorkbook.Unprotect
                
                    'Unhides all the worksheets in other workbook
                    For Each Sh In wb.Worksheets
                        Sh.Visible = True
                    Next
                    
                    'compares the sheets(sh) name in the other workbook with my workbook sheets and if it matches it overwrites the data in there first
                    For Each Sh In wb.Worksheets
                        For Each ws In ThisWorkbook.Worksheets
                        
                            'if there is an error the macro will go to the nextsheet: error or otherwise just skip the sheet
                            On Error GoTo nextsheet:
                            
                            If Sh.Name = ws.Name Then
                           
                           
                                    Sh.Range("A1:EF200").Copy
                                    ws.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
                                    '!!!This changes the tab colour, change this to have an if formula where if the colour is light change to other and if other change to light
                                     If ws.Tab.ThemeColor = xlThemeColorLight1 Then
                                    With ws.Tab
                                        .ThemeColor = xlThemeColorDark1
                                    End With
                                    Else
                                    With ws.Tab
                                        .ThemeColor = xlThemeColorLight1
                                    End With
                                    End If

                            End If
                            
                            
nextsheet: Resume nextsheet2
nextsheet2:  Next ws
    
                   Next
                  

                Application.CutCopyMode = False
        wb.Close False
    
End Sub

 

[Warship]

UNTESTED

Try:

Sub openQDDATA()
    Dim opan As String
    Dim QDtabs As Variant
    
    Dim wb As Object
    Dim Sh As Variant
    Dim ws As Worksheet
    Dim myrng2 As Range
    
    Dim SheetsNotChanged As String
    
    
    ThisWorkbook.Activate
    Sheets("CHECK LIST").Visible = True
    Sheets("CHECK LIST").Select
    
    Range("c2:c2000").ClearContents
    
    'this array has all the back tab names of the other worksheet
    QDtabs = Array(Sheets("lo"), Sheets("ki"), Sheets("gj"), Sheets(""), Sheets("m"), _
    Sheets("n"), Sheets("b"), Sheets("v"), Sheets("x"), Sheets("z"), Sheets("a"), Sheets("q"), _
    Sheets("w"), Sheets("e"), Sheets("f"), Sheets("g"), Sheets("h"), _
    Sheets("frtg"), Sheets("gtrew"), Sheets("hjku"), Sheets("care"), Sheets("js"), Sheets("Myt"), _
    Sheets("we"), Sheets("hgfds"), Sheets("vfds"), Sheets("bg"), Sheets("fn"), _
    Sheets("de"), Sheets("fr"), Sheets("gr"), Sheets("kiu"), Sheets("HIP"), Sheets("loe"), _
    Sheets("lop"), Sheets("ki"), Sheets("po"), Sheets("plk"))
    
    'Makes all the qdtab worksheets in thisworkbook visible
    For Each Sh In QDtabs
        Sh.Visible = True
    Next
    
    'sets whatever file is selected as a variable opan
    opan = Application.GetOpenFilename
    'opens opan and puts it in the variable wb
    Set wb = Workbooks.Open(opan)
    
    'unprotects other workbook
    ActiveWorkbook.Unprotect
    
    'Unhides all the worksheets in other workbook
    For Each Sh In wb.Worksheets
        Sh.Visible = True
    Next
    
    'compares the sheets(sh) name in the other workbook with my workbook sheets and if it matches it overwrites the data in there first
    For Each Sh In wb.Worksheets
        For Each ws In ThisWorkbook.Worksheets
        
            'if there is an error the macro will go to the nextsheet: error or otherwise just skip the sheet
            On Error GoTo HandleError
            
            If Sh.Name = ws.Name Then
                Sh.Range("A1:EF200").Copy
                ws.Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
                
                '!!!This changes the tab colour, change this to have an if formula where if the colour is light change to other and if other change to light
                With ws.Tab
                    If .ThemeColor = xlThemeColorLight1 Then
                        .ThemeColor = xlThemeColorDark1
                    Else
                        .ThemeColor = xlThemeColorLight1
                    End If
                End With
            End If
        
nextsheet:
        Next ws
    
    Next Sh
    
    
    Application.CutCopyMode = False
    wb.Close False
    SheetsNotChanged = Mid(SheetsNotChanged, 2)
Exit Sub
HandleError:
    SheetsNotChanged = "," & ws.Name
    Resume nextsheet
End Sub