Modify VBA Code To Ignore Missing Worksheets

[excelbytes]

I have a workbook called "Consolidated HR Report". In it there are 4 worksheets. One is called "Training". I have another workbook called "AllTraining", which has between 8 to 11 worksheets. Examples of the names of those worksheets are "Co_Training", "Fi_Training", "Gr_Training", etc. I open both workbooks and run the code that's in the Consolidated workbook, and what that does is goes into each of the 8 to 11 worksheets in the AllTraining workbook and copies the data into specific areas of the Consolidated workbook. However, There aren't always all of the 11 "locations" in the "AllTraining" workbook. How do I modify the code to check to see if that worksheet is in the "AllTraining" workbook, and if not, skip to the next worksheet?

I hope that makes sense. Here is a partial sample of the VBA code:

    Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining.xlsm").Activate
    Sheets("Co_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("C35").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining.xlsm").Activate
    Sheets("Fi_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("C63").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining.xlsm").Activate
    Sheets("Gr_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("C91").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining.xlsm").Activate

 

[jolivanes]

bebo021999 has "paste it in the appropriate section" figured out.

Sub Or_So()
Dim wb1 As Workbook, wb2 As Workbook, i As Long, shArr
Set wb1 = Workbooks("Consolidated HR Report.xlsm")    '<---- Check and change name if required
Set wb2 = Workbooks("AllTraining X.xlsm")    '<---- Check and change name if required
shArr = Array("Sheet4", "Sheet9", "Sheet6", "Sheet12", "Sheet8")    '<----- Change and add as many as required
    For i = LBound(shArr) To UBound(shArr)
        On Error Resume Next
            If Not wb2.Sheets(shArr(i)) Is Nothing Then wb1.Sheets("Sheet2").Cells(Rows.Count, 1).End _
            (xlUp).Offset(1).Resize(23, 12).Value = wb2.Sheets(shArr(i)).Cells(7, 3).Resize(23, 12).Value
        On Error GoTo 0
    Next i
End Sub

 

[excelbytes]

rlv01:

I ran that code you provided and got this result:

Which is correct. There are 9 out of 13 worksheets in the AllTraining X.xlsm workbook, and of the 3 you provided the code for, one (Gr_X_Training) was missing while the other two were found.

I think I see where I am mistaken. I took the code you provided and ran it step by step and it just skipped over each worksheet, but when I just ran the code it does in fact work correctly! Can you explain to me why that is?

 

[rlv01]

Which is correct. There are 9 out of 13 worksheets in the AllTraining X.xlsm workbook, and of the 3 you provided the code for, one (Gr_X_Training) was missing while the other two were found.

Why is sheet Gr_X_Training missing? Remember, these are not my names, they are the ones you gave to me.

I think I see where I am mistaken. I took the code you provided and ran it step by step and it just skipped over each worksheet, but when I just ran the code it does in fact work correctly! Can you explain to me why that is?

I suspect because you are misinterpreting how the Select Case function works. You are looping through all the worksheets and testing to see if each worksheet name ( WS.Name) matches the Case of "Co_X_Training". If no match, then the code beneath Case "Co_X_Training" will be skipped. When it does match, it will not be skipped.