Code fails to work on copied worksheet

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,640
Office Version
  1. 2007
Platform
  1. Windows
Afternoon.

In a workbook i have 12 worksheets.

This code is in each worksheet "right click view code/paste"
Code:
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

Code:
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
996
Office Version
  1. 2010
Platform
  1. Windows
Put a break point on the For i= line and see if the event is being triggered.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
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?
 
Last edited:

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Instead of having the code in each sheet module perhaps you could use the ThisWorkbook SheetActivate event.
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,640
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,640
Office Version
  1. 2007
Platform
  1. Windows
Instead of having the code in each sheet module perhaps you could use the ThisWorkbook SheetActivate event.
Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You posted the original code :eek:
I think you need something like this to achieve "If no values in column A then select cell A3"
Code:
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 :LOL::LOL:
 
Last edited:

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,640
Office Version
  1. 2007
Platform
  1. Windows
Can you then advise how i add you code for cell A3 into Nories code.

Thankds
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,762
Office Version
  1. 365
Platform
  1. Windows
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,640
Office Version
  1. 2007
Platform
  1. Windows
That did the trick.
Many thanks people.

Have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,109,165
Messages
5,527,179
Members
409,750
Latest member
BorisYeltsin

This Week's Hot Topics

Top