Code fails to work on copied worksheet

[ipbr21054]

Afternoon.

In a workbook i have 12 worksheets.

This code is in each worksheet "right click view code/paste"

Private Sub Worksheet_Activate()    For i = 28 To 4 Step -1
        If Cells(i - 1, "A") <> "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next
End Sub

This code is in ThisWorkbook

Private Sub Workbook_Open()    Dim ws As Worksheet
Set ws = ActiveSheet
Application.ScreenUpdating = False
Sheets("LIST").Activate
ws.Activate
Application.ScreenUpdating = True
End Sub

I have copied this workbook & kept it as a template etc.
Looking through each page the code does what iyt should do all apart from my MILEAGE sheet.
I can select any cell,leave the page,come back to this page & the same cell is selected and not the cell that should be selected.

Going back to the sheet it was copied from it works fine.
Ive copied the code for that sheet from original to template but still wont work.
I even just copied the original & deleted all the values in each cell on each page,remember this was working,i then go to the MILEAGE page and not it will not work

 

[NoSparks]

Put a break point on the For i= line and see if the event is being triggered.

 

[Yongle]

If all other sheets in the workbook work as expected, then events are being triggered and, therefore, the problem must be in sheet MILEAGE or its code

4 things to check:

1 Procedure is missing : Does the sheet module for "MILEAGE" contain procedure Worksheet_Activate ?
(make sure - right-click on sheet tab \ view code \ is it there?)
2 Code has been modified : Has the procedure in that sheet module in been modified in any way?
(delete the code and copy it in again)
3 There is nothing in range A3:A27 : Is there at least ONE value within that range?
4 Is sheet MILEAGE protected?

 

[Norie]

Instead of having the code in each sheet module perhaps you could use the ThisWorkbook SheetActivate event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim I As Long

    For I = 28 To 4 Step -1
        If Sh.Cells(I - 1, "A") <> "" Then
            Sh.Cells(I, "A").Select
            Exit For
        End If
    Next I
    
End Sub

 

[ipbr21054]

Hi,
One thing i didnt check,must have a value in column A for it to start with.
Now works ok.

Its strange because that doesnt happen on the other sheets,i can select Z10000 & come to the page where a cell in column A is selected by the code.

Anyway can we have a work around for this sheet.

Open the sheet,
If no values in column A then select cell A3
If there are values in column A then select the next available cell.

This way it should work

Private Sub Worksheet_Activate()    For i = 28 To 4 Step -1
        If Cells(i - 1, "A") <> "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next
End Sub

 

[ipbr21054]

Instead of having the code in each sheet module perhaps you could use the ThisWorkbook SheetActivate event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim I As Long

    For I = 28 To 4 Step -1
        If Sh.Cells(I - 1, "A") <> "" Then
            Sh.Cells(I, "A").Select
            Exit For
        End If
    Next I
    
End Sub

This also works & might be better that placing in every sheet.
I have the same issue for the MILEAGE sheet
If i select Z1000 etc on every sheet the manually view each sheet i then see the first cell in column A is selected.
This doesnt happen on the MILEAGE sheet & cell Z1000 is still selected.

 

[Yongle]

You posted the original code
I think you need something like this to achieve "If no values in column A then select cell A3"

Private Sub Worksheet_Activate()
    Range("A3").Select
    For i = 28 To 4 Step -1
        If Cells(i - 1, "A") <> "" Then
            Cells(i, "A").Select
            Exit For
        End If
    Next
End Sub

BUT also
- read post from @Norie
- it is a more efficient approach


EDIT - ah - I see you already did

 

[ipbr21054]

Can you then advise how i add you code for cell A3 into Nories code.

Thankds

 

[Yongle]

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim I As Long
    [COLOR=#ff0000]Sh.Cells(3, "A").Select[/COLOR]
    For I = 28 To 4 Step -1
        If Sh.Cells(I - 1, "A") <> "" Then
            Sh.Cells(I, "A").Select
            Exit For
        End If
    Next I
End Sub

 

[ipbr21054]

That did the trick.
Many thanks people.

Have a nice day