Not completing Sub - skipping to another Sub

[Ippon]

Hi - looking for some help again. I have the following coding that I have tried in both the Sheet code and as a module at different times.

Sub CopyEmployed()
Sheets("Employment").Select
Range("A3:L200").Select
Selection.ClearContents
Range("D4").Select
Sheets("Act1").Select

- And there will be more here when I can get this first part working-

When I step through this works up to "Selection.ClearContents" but then ignores the next two lines

Range("D4").Select
Sheets("Act1").Select

and goes straight to the Private Sub coding below that I am using to change the sheet tab names (This is in the ThisWorkbook section and works perfectly)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim LinkedCells As Range
Dim ArrayOfMatchingSheets As Variant
Dim i As Long
Set LinkedCells = Sheets("Menu").Range("E8:E22")
ArrayOfMatchingSheets = Array(Sheet8, Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet19, Sheet20, Sheet21, Sheet22)
If Sh.Name = LinkedCells.Parent.Name Then
If Not Application.Intersect(LinkedCells, Target) Is Nothing Then
For i = 0 To 14
On Error Resume Next
ArrayOfMatchingSheets(i).Name = CStr(LinkedCells.Cells(1, 1).Offset(i, 0).Value)
If ArrayOfMatchingSheets(i).Name <> CStr(LinkedCells.Cells(1, 1).Offset(i, 0).Value) Then Beep
On Error GoTo 0
Next i
End If
End If
End Sub

I can continue to step through this until it eventually returns to

Range("D4").Select
Sheets("Act1").Select

It may then run the remainder of the coding but what I have tried so far produces more Runtime and type mismatch errors which I will post after I can fix this.

Bottom line is I dont want the change sheet tab code run - I need the remainder of the Sub CopyEmployed() to run.

Any ideas please?

 

[GTO]

That's clever Mark

I would not have thought of doing it that way. But I will

Hey Peter, thank you very much.

Thanks GTO. I am confused, could you explain a bit further

I don't know what Select Case wks.CodeName means and do I change
Case Sheet2.CodeName, Sheet3.CodeName, Sheet4.CodeName to be
Case Sheet8.sheet8, Sheet9.sheet9, Sheet10.sheet10 ?

Hi Ippon,

You will want to look at the Help topics for both CodeName and the Select Case Statement, but see if this helps as well.

Option Explicit
    
Sub CopyRows()
Dim wks     As Worksheet
Dim rng     As Range
Dim Cell    As Range
    
    '// Loop through the Collection of worksheets within the workbook.                  //
    For Each wks In ThisWorkbook.Worksheets
        '// See Help for CodeName Property.  This returns a String, like: "Sheet10"     //
        Select Case wks.CodeName
        '// See Help for Select.  Sort of IF on steroids.  Instead of something like:   //
        '//    If wks.CodeName = "Sheet8" _                                             //
        '//    Or wks.CodeName = "Sheet9" _                                             //
        '//    Or wks.CodeName = "Sheet10" Then                                         //
        '//                                                                             //
        '// Which is the same thing as ...                                              //
        '//    If wks.CodeName = Sheet8.CodeName _                                      //
        '//    Or wks.CodeName = Sheet9.CodeName _                                      //
        '//    Or wks.CodeName = Sheet10.CodeName Then                                  //
        '//                                                                             //
        '// ...in using Select, each Case can test against a range of values or several //
        '// different values to see if there is a "match".                              //
        Case Sheet8.CodeName, Sheet9.CodeName, Sheet10.CodeName '<---etc...
            '// I happen to use .Cells, but the results are the same.  One advantage to //
            '// using .Cells is that both the Row and Column arguments can be numerical //
            '// values, such as we could have used '8' (with no quote marks) instead    //
            '// of "H" (which requires the quote marks).  This can come in handy if we  //
            '// are wanting to set the range referenced based on dynamic conditions (such//
            '// as more or less rows or columns of data being found).                   //
            Set rng = wks.Range(wks.Cells(1, "H"), wks.Cells(wks.Rows.Count, "H").End(xlUp))
            
            For Each Cell In rng.Cells
                If Cell.Value = "Employment" Then
                    wks.Range(wks.Cells(Cell.Row, "A"), _
                              wks.Cells(Cell.Row, "L")).Copy _
                                Destination:=wks.Parent.Worksheets("Employment").Cells(wks.Rows.Count, 1).End(xlUp).Offset(1)
                End If
            Next
        End Select
    Next
End Sub

Hope that helps,

Mark

 

[Ippon]

Mark and all others I really do appreciate the time you have given to me to explain all this. I will digest it all and check out the leads you have given me.
You guys on this forum are a woderful help. I will let you know how I go or what trouble I'm in!

 

[Ippon]

Thanks GTO this all works perfectly - all you guys are amazing - magicians. Now to start learning how you do it.