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

 

[rlv01]

In general it works like this:

Sub Demo()
    Dim WB As Workbook
    Dim WS As Worksheet

    Set WB = Application.Workbooks("AllTraining.xlsm")
    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Co_Training", "Fi_Training", "Gr_Training" '<<< list of worksheets to process.
            '
            ' Code to process worksheets goes here
            '
        End Select
   Next WS
End Sub

or if each worksheet needs different code, then

Sub Demo2()
    Dim WB As Workbook
    Dim WS As Worksheet

    Set WB = Application.Workbooks("AllTraining.xlsm")
    For Each WS In WB.Worksheets
        Select Case WS.Name
        Case "Co_Training"                            '<<< list of worksheets to process.
            '
            ' Code to process worksheet "Co_Training"  goes here
            '
        Case "Fi_Training"                            '<<< list of worksheets to process.
            '
            ' Code to process worksheet "Fi_Training" goes here
            '
        Case "Gr_Training"                            '<<< list of worksheets to process.
            '
            ' Code to process worksheet "Gr_Training" goes here
            '
        End Select
    Next WS
End Sub

 

[jolivanes]

One way would be like so. Code assumes that both Workbooks are open.
Change references where required.

Sub Check_For_Sheets()
Dim shArr, i As Long, ws As Worksheet, wb As Workbook
shArr = Array("Sheet1", "Sheet2", "Sheet5", "Sheet14")    '<---- Sheets you want data from
Set wb = Workbooks("AllTraining.xlsm")    '<---- Workbook that is open ans has all the sheets including the sheets in the shArr
    For i = LBound(shArr) To UBound(shArr)
        For Each ws In wb.Worksheets
            If ws.Name = shArr(i) Then
                MsgBox ws.Name & " " & ws.Range("A1").Value    '<---- Change this line to what you want to do with the data
            End If
        Next ws
    Next i
End Sub

 

[excelbytes]

I think I may have some lines of code out of order. Here is the first section of my code:

Sub CopyTrainingX()

    Dim WB As Workbook
    Dim WS As Worksheet

    Set WB = Application.Workbooks("AllTraining X.xlsm")
    For Each WS In WB.Worksheets
        Select Case WS.Name
    
    Case "Co_X_Training"
    Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining X.xlsm").Activate
    Sheets("Co_X_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("R35").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False
       
    Case "Fi_X_Training"
    Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining X.xlsm").Activate
    Sheets("Fi_X_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("R63").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    Case "Gr_X_Training"
    Sheets("Training").Select
    Range("A1").Select
    Windows("AllTraining X.xlsm").Activate
    Sheets("Gr_X_Training").Select
    Range("C7:N29").Select
    Selection.Copy
    Windows("Consolidated HR Report.xlsm").Activate
    Range("R91").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
       :=False, Transpose:=False

The intent of the code is to go to the "AllTraining X.xlsm" workbook to check to see if each of the worksheets (e.g. "Co_X_Training", "Fi_X_Training", "Gr_X_Training", etc.) exists, and if it does, copy the range C7:N29, then go back to the "Consolidated HR Report.xlsm" workbook on the "Training" worksheet and paste it in the appropriate section for each of the various worksheets from the "AllTraining X.xlsm" workbook. Right now it's just going to each "Case..." line of the code and skipping the lines below it to the next worksheet. What do I need to rearrange in the code to make that work?

 

[jolivanes]

Re: "I think I may have some lines of code out of order"
You need to let possible helpers know which workbooks, all their names, not some names and then etc, need checking if they exist and if they are open or not.
If they are not open, need to know the path to these workbooks.
Need to know where to paste. "Appropriate section" is not a well known range.

BTW, all the "Selects" are slowing down code and are not required.

 

[excelbytes]

I apologize but I think I'm not communicating the issue correctly. There are only two workbooks: "Consolidated HR Report.xlsm" which is where the code is located & "AllTraining X.xlsm" which contains all the worksheets that I'm trying to check to see if they exist. Both workbooks will be open. Currently I've been commenting out (putting an " ' ") before each line of the code that doesn't have an associating worksheet in the "AllTraining X.xlsm" workbook, and the code is working fine. However, from month to month, the list of worksheets varies, so that process is not practical. So I need the code to look at the "AllTraining X.xlsm" workbook for each of the worksheets one at a time, and then, one by one, copy the appropriate section into the "Training" worksheet on the "Consolidated HR Report.xlsm" workbook for those that do exist.

What I think is happening is that the code is looking for a "Co_X_Training" worksheet in the Consolidated HR Report.xlsm" workbook, not the "AllTraining X.xlsm", and since it doesn't find it, just goes to the next section.

 

[rlv01]

What about something like this?

Sub CopyTrainingX()
    
    Dim WB As Workbook
    Dim WBDest As Workbook
    Dim WS As Worksheet
    
    Set WB = Application.Workbooks("AllTraining X.xlsm")
    Set WBDest = Application.Workbooks("Consolidated HR Report.xlsm")
    
    For Each WS In WB.Worksheets
        Select Case WS.Name
            Case "Co_X_Training"
                WS.Activate
                WS.Range("C7:N29").Copy
                WBDest.Worksheets("Training").Range("R35").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
            Case "Fi_X_Training"
                WS.Activate
                WS.Range("C7:N29").Copy
                WBDest.Worksheets("Training").Range("R65").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                
            Case "Gr_X_Training"
                WS.Activate
                WS.Range("C7:N29").Copy
                WBDest.Worksheets("Training").Range("R91").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End Select
    Next WS
End Sub

 

[excelbytes]

riv01: Thank you so much for your code. It is clearly much less complex than my limited knowledge of VBA could have produced. However, it's still not quite right.

I have both files open, and as I've said before, the code is in the "Consolidated HR Report.xlsm" workbook. When I go through the code line by line, it gets to Case "Co_X_Training" then just skips to Case "Fi_X_Training" and right down the line, passing the lines of code to copy and paste. It's as if it's not focused on the correct workbook when searching for the various worksheets and assuming they are not available and skips them.

Any thoughts?

 

[rlv01]

You did tell me that the code is in the "Consolidated HR Report.xlsm" workbook. But you also said this:

I need the code to look at the "AllTraining X.xlsm" workbook for each of the worksheets one at a time

Which is what the code I posted does. If it is "skipping over" it is because it is not finding a match for any worksheets named Co_X_Training", "Fi_X_Training", or "Gr_X_Training" in workbook "AllTraining X.xlsm". This is likely because your assumptions about what is true do not match what is actually true. A common programming problem. Put this test code in workbook Consolidated HR Report.xlsm and run it to see if it sheds any light.

Sub CopyTest()
    Dim WB As Workbook
    Dim WS As Worksheet
    Dim S As String
    Dim a, b, c

    Set WB = Application.Workbooks("AllTraining X.xlsm")

    a = "Worksheet 'Co_X_Training' is missing" & vbCr
    b = "Worksheet 'Fi_X_Training' is missing" & vbCr
    c = "Worksheet 'Gr_X_Training' is missing" & vbCr

    S = "VBA code found in workbook: " & ThisWorkbook.Name & vbCr & vbCr
    S = S & "List of worksheets found in workbook :" & WB.Name & vbCr
    For Each WS In WB.Worksheets
        S = S & "'" & WS.Name & "'" & vbCr
        Select Case WS.Name
        Case "Co_X_Training"
            a = "Worksheet '" & WS.Name & " found!" & vbCr
        Case "Fi_X_Training"
            b = "Worksheet '" & WS.Name & " found!" & vbCr
        Case "Gr_X_Training"
            c = "Worksheet '" & WS.Name & " found!" & vbCr
        End Select
    Next WS
    S = S & vbCr & a & b & c
    MsgBox S
End Sub

 

[bebo021999]

May be:

Option Explicit
Sub Copy()
Dim i&, k&
Dim wbT As Workbook, shName, rng
Dim sh As Worksheet, ws As Worksheet
Set wbT = Workbooks("AllTraining X.xlsm")
Set sh = Workbooks("Consolidated HR Report.xlsm").Sheets("Training")
shName = Array("Co_X_Training", "Fi_X_Training", "Gr_X_Training", "sample_1", "sample_2", "sample_3") 'add more sheets those need to copy...(upto 11?)

'loop thru each sheet of "AllTraining X" sheet, if sheetname match array shName then copy/paste
For Each ws In wbT.Sheets
    For i = 0 To UBound(shName)
        If ws.Name = shName(i) Then
            rng = ws.Range("C7:N29").Value
            k = k + 1
            sh.Range("R" & 35 + (k - 1) * 28).Resize(UBound(rng), UBound(rng, 2)).Value = rng ' starts from R35, each group has 28 rows
            Exit For
        End If
    Next i
Next ws
End Sub