VBA ScrollArea = "Range"

[Pinaceous]

Hi All,

Is there a way to VBA ScrollArea all of the EVEN sheet numbers to

"A1:L80"

AND ScrollArea all of the ODD sheet numbers to

"A1:BK80"

??


Thank you!

Pinaceous

 

[mrshl9898]

Sub SheetScroll()


Dim i As Long


For i = 1 To Sheets.Count Step 2
    Sheets("Sheet" & i).ScrollArea = "A1:L80"
Next i


For i = 2 To Sheets.Count Step 2
    Sheets("Sheet" & i).ScrollArea = "A1:BK80"
Next i


End Sub

 

[Pinaceous]

Hi mrshl9898,

Thank you! I can't wait to try it out!

Pin

 

[Pinaceous]

Hi mrshl9898,

I'm getting a VBA error:

Run-time '9':
Subscript out of range

Do you know what could be causing this error?

Thanks,
Pin

 

[Pinaceous]

VBA scroll area for odd sheets

Hi All,

I'm trying to figure out how to set a scroll area for all of my odd sheet numbers in my workbook.

I'm using this code that doesn't work:

Sub SheetScroll()


Dim i As Long


For i = 1 To Sheets.Count Step 2
    Sheets("Sheet" & i).ScrollArea = "A1:L80"
Next i

End Sub

VBA providers an error that highlights the second line.

Any suggestions?

Thanks,
Pinaceous

 

[Fluff]

Re: VBA scroll area for odd sheets

Try

    Sheets(i).ScrollArea = "A1:L80"

 

[My Aswer Is This]

Re: VBA scroll area for odd sheets

Thy this:

Sub SheetScroll()
Dim i As Long
    For i = 1 To Sheets.Count Step 2
        Sheets(i).ScrollArea = "A1:L80"
    Next i
End Sub

 

[My Aswer Is This]

Re: VBA scroll area for odd sheets

After testing this I realized you need to activate each sheet for this to work.
And this setting is not saved. You must run this script every time you open your workbook.
I had seen postings before about this problem.

Try this script. But you have to run it each time you open your Workbook.

Sub SheetScroll()
Dim i As Long
    For i = 1 To Sheets.Count
        
        Sheets(i).Activate
        Sheets(i).ScrollArea = "A1:L80"
        
    Next i
End Sub

 

[mrshl9898]

Try this mate.

It was looking at the name of the sheet previously "Sheet1", "Sheet2" etc. Instead of the index number.

Sub SheetScroll()




Dim i As Long




For i = 1 To Sheets.Count Step 2
    Sheets(i).ScrollArea = "A1:L80"
Next i




For i = 2 To Sheets.Count Step 2
    Sheets(i).ScrollArea = "A1:BK80"
Next i




End Sub

 

[Fluff]

Re: VBA scroll area for odd sheets

@MAIT
Whilst I agree that the scrollarea is lost when you close the workbook, for me it works on on inactive sheets.
I'm running the 32bit version of 2013 on win7, what are you running?