Runtime Error 1004 - Active method of range class failed - multiple sheets

[MARK1111]

Good evening all,

I've had a read through various posts but due to my lack of knowledge, it is difficult to determine if others are have the same runtime error as me or not. If anyone has any suggestions, I'd be grateful.

I have a workbook, that originally contained just the one sheet. The intention was always to increase the sheets (one per customer). I asked someone to tweak the sheet so that when the sheet was selected, it automatically scrolled to the correct week. I had some success prior to this using code I found online but the issue I faced was that it assumed the week always started on a Sunday, hence the reason I sought assistance.
After a number of emails, it was clear the person who came up with the code, would no longer assist.
The issue arose when I added further sheets to the workbook. I get the following message:

'Run-time error 1004. Active method of range class failed'

Here is the code that is causing the problems:

 Private Sub Workbook_Open()

Application.EnableEvents = False
Dim weekNum As Long


weekNum = VBAWeekNum(Now(), 1)


Dim wkDay As Long
wkDay = Weekday(Now, vbMonday)


Sheet1.Cells((3 + weekNum) + (weekNum - 1), 3 + wkDay).Activate
Sheet1.Activate
ActiveWindow.ScrollRow = (3 + weekNum) + (weekNum - 1)
'(i + 3) + (i - 1)
Application.EnableEvents = True


End Sub

As I've said, my knowledge is little to nothing so when something goes wrong, I'm lost. Hopefully its something obvious and simple but the fact he refused to assist would lead me to believe it's more time consuming to fix.

Thanks in advance,

Mark

 

[Norie]

Mark

The code you posted is for the Workbook open event, not a sheet activate event.

When do you actually want it to be executed and on which sheet?

 

[MARK1111]

Hi Norie,

Thanks. See, I told you my knowledge is limited!!
I want it to execute across all sheets excluding one. Currently, there are about 80 identical sheets listing what each customers owes each day and when they pay. So essentially, other than the amounts and their name, the sheets are identical. The only sheet that the code doesn't need to execute on is a summary sheet that pulls the outstanding amounts owed. Ideally, it would run when the workbook is opened.

 

[Norie]

Mark

You could run code when the workbook opens to move to the appropriate location in each sheet but it would involve looping through and activating all the sheets.

With 80 sheets that would take some time, and it might also be a little disconcerting for the user to see.

Alternatively you could use the workbook level event SheetActivate:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

End Sub

This event is triggered whenever a sheet in the workbook is activated and is passed an argument, Sh, that references the sheet that's been activated.

The first thing to do would be to check that the sheet Sh refers to isn't the summary sheet.

You could do that with something like this.

If Sh.Name = "Summary" Then Exit Sub ' if summary sheet is activated do nothing

' code to activate appropriate row on activated sheet

For the second part, i.e. scrolling to the appropriate week on the sheet, I think we would need to more information on the sheet layout.

 

[MARK1111]

Okay, I understand the issue you are describing. It tends to do that when I ask it to execute something across a batch of sheets. Would it help if I uploaded a copy of the workbook here, with just a few sheets, to give you a better idea of what I'm using it for? I have a copy I'm working on at the minute that has all the sensitive data removed.

 

[Norie]

You can't upload files here I'm afraid.

I think I know what you are looking for, I use something similar myself though on a smaller scale and using a button on the sheet(s) to move the appropriate location (based on date).

 

[MARK1111]

Hopefully, I've added an image to this post to give you a better idea of how it looks. Please brace yourself for some garish colours. At the time, because it isn't me using it, I needed others to be able to easily tell the difference between the weeks to avoid errors. It is on my list of changes to make, to tone down the color scheme! Would it be easier to do something like you have done and add a button along the top row, to have it jump to the correct row?

 

[MARK1111]

The image doesn't appear to have worked. I'm not sure if URL's are allowed but this is the link I was trying to add:

https://ibb.co/DY4k6vj

 

[MARK1111]

Just a quick question based on how you say you do it - does it cause an issue during a leap year or is that taken into account?

 

[DanteAmor]

Change your code to the following

Private Sub Workbook_Open()
    Dim s As Long, u As Long
    WeekNum = WorksheetFunction.WeekNum(Now(), 1)
    ActiveWindow.FreezePanes = False
    Range("A4").Select
    ActiveWindow.FreezePanes = True
    u = Range("B" & Rows.Count).End(xlUp).Row
    Set b = Range("B4:B" & u).Find(WeekNum, LookIn:=xlValues, lookat:=xlWhole)
    If Not b Is Nothing Then
        ActiveWindow.SmallScroll Down:=b.Row - 4
    End If
End Sub

Try an tell me.