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

MARK1111

New Member
Joined
Jan 23, 2019
Messages
12
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:

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

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"

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 23, 2019
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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:
Code:
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.
Code:
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

New Member
Joined
Jan 23, 2019
Messages
12
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
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

New Member
Joined
Jan 23, 2019
Messages
12
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

New Member
Joined
Jan 23, 2019
Messages
12
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,782
Office Version
2007
Platform
Windows
Change your code to the following

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

Watch MrExcel Video

Forum statistics

Threads
1,099,507
Messages
5,469,029
Members
406,628
Latest member
jared92

This Week's Hot Topics

Top