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?

 

[Michael M]

Works fine for me...
Keep in mind if "Employment" isn't the active sheet, it will select D4 in whatever sheet is active
Sorry, didn't read ALL the message
When it goes to Employment to clear the cells....wouldn't the change then trigger the Sheet change event....as it should

 

[Sektor]

Sub CopyEmployed()
    [B]Application.EnableEvents = False[/B]
    Sheets("Employment").Select
   ' Lots of code
   '......
   '
   Application.EnableEvents = True
End Sub

 

[Ippon]

Hi Michael and Sektor - thank you for your help.

The sheet name change event is triggered by entering data in the "Menu" sheet so hopefully not by any changes to the Ëmployment" sheet".

But I don't know much about all this stuff. All the code I use comes from asking questions on the forum then playing with it to try and understand what's happening - I just play till something works. A problem I face is using so many codes or subs I am never sure if they conflict or are pasted into the right place.
I will try the suggestions from you both and reply again. Thanks again.
John D

 

[Sektor]

In your code all things happens on "Employment" sheet. See comments.

Sub CopyEmployed()
    
    ' Sheet "Employment" is selected.
    Sheets("Employment").Select
    
    ' Range("A3:L200") on "Employment" sheet is selected.
    Range("A3:L200").Select
    
    ' Here you clear Range("A3:L200").
    ' Now Worksheet_Changed event of "Employment" sheet is triggered.
    Selection.ClearContents
    
    Range("D4").Select
    Sheets("Act1").Select
    
End Sub

 

[Ippon]

Thanks Michael & Sektor for your explainations - I did not understand this triggered the worksheet change event. This part is working now and here is the code. So I now have three other problems if you can help please.

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

Which calls this

Sub CopyRowsSht8()
Worksheets("Act1").Select
Set Rng = Worksheets("Act1").Range(Worksheets("Act1").Range("H1"), Worksheets("Act1").Range("H" & Rows.Count).End(xlUp))
For Each cell In Rng

If cell.Value = "Employment" Then
num = cell.Row

Worksheets("Act1").Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next

Call CopyRowsSht9

End Sub

1. This works but problem one is that I want to use the sheet code name (sheet8) and not the user name ("ACT1") because the sheet names will change at times. I have changed all references to ("ACT1") like this:

Sub CopyRowsSht8()
Worksheets(Sheet8).Select
Set Rng = Worksheets(Sheet8).Range(Worksheets(Sheet8).Range("H1"), Worksheets(Sheet8).Range("H" & Rows.Count).End(xlUp))
For Each cell In Rng .... etc etc

This stops with runtime error 13 Type mismatch at the line
Worksheets(Sheet8).Select
so I figure that this is not how I should refer to the Sheet code ??

2. The second problem is that when working this calls CopyRowsSht8 and this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to do sheets 8 to 22.

3. Third problem - It takes a while to do just two sheets so 15 would be slow.

 

[Ippon]

Hey I got part 1 working now - took out refrences to Worksheets ie

<TABLE style="WIDTH: 257pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=343><COLGROUP><COL style="WIDTH: 257pt; mso-width-source: userset; mso-width-alt: 12544" width=343><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl63 height=20 width=343>Sub CopyRowsSht8()</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>Sheet8.Select</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=34 width=343>Set Rng = Sheet8.Range(Sheet8.Range("H1"), Sheet8.Range("H" & Rows.Count).End(xlUp))</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>For Each cell In Rng</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=343> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>If cell.Value = "Employment" Then</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>num = cell.Row</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=343> </TD></TR><TR style="HEIGHT: 51pt" height=68><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 51pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=68 width=343>Sheet8.Range("A" & num & ":" & "L" & num).Copy Destination:=Worksheets("Employment").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>End If</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>Next</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=343> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl64 height=20 width=343>Call CopyRowsSht9</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl65 height=20 width=343> </TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 257pt; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl66 height=21 width=343>End Sub</TD></TR></TBODY></TABLE>

I would like to know if this code looks OK and understand why this change worked to help my learning if anyone can explain.
That leaves me with just problem 2 and 3

2. The second problem is that when working this calls CopyRowsSht8 and this calls CopyRowsSht9 and so on. I have 15 sheets so how would I code this into some kind of loop to do sheets 8 to 22.

3. Third problem - It takes a while to do just two sheets so 15 would be very slow. The maximun number of rows containing data is only about 100 on each sheet -could that make a difference?

 

[GTO]

Just as to looping through the sheets and doing stuff if the individual sheet is one of the ones we want, change codenames to suit:

Option Explicit
    
Sub CopyRows()
Dim wks As Worksheet
Dim rng As Range
    
    For Each wks In ThisWorkbook.Worksheets
        Select Case wks.CodeName
        Case Sheet2.CodeName, Sheet3.CodeName, Sheet4.CodeName
            ' Set rng = ...
            ' remainder of code, using wks.whatever instead of the sheet's codename
        End Select
    Next
End Sub

 

[VoG]

That's clever Mark

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

 

[Ippon]

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 ?

In your reply you say change codenames to suit but in the VBA comments say "using wks.whatever instead of the sheet's codename".
I am a real beginner - Not sure what I should be doing at all - with a few more clues I may be able to get it.

Sorry VoG for the multiple entry - learning every day.