ALL WORKSHEETS SCROLLED TO TOP AND CELL A1 ACTIVE

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
I am trying to write a macro that will scroll all worksheets in a workbook to the top and leave the cursor in cell A1. The number of sheets in each workbook may vary so, the macro must work no matter how many sheets. Any help is appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

How about this:
Code:
Sub ToTop()
Dim sCurCell As String
Dim WS As Worksheet, wsCur As Worksheet

Set wsCur = ActiveSheet
sCurCell = Selection.Address

For Each WS In ThisWorkbook.Worksheets
    WS.Select
    WS.Range("A1").Select
Next WS
wsCur.Select
Range(sCurCell).Select
End Sub
 

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
GOT ERROR MESSAGE

hi alan, i get this error message:

METHOD SELECT OF OBJECT WORKSHEET FAILED
 

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
i think its my hidden sheets that are causing the error. how do i get it to avoid hidden sheets?
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

Hi,

One way would be to just put 'On Error Resume Next' before the For loop
 

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
DOESNT WORK

the macro does not do anything when I put the "on error resume next" line before the for loop
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494

ADVERTISEMENT

Oh yes it does :¬)

Code:
Sub ToTop()
Dim sCurCell As String
Dim WS As Worksheet, wsCur As Worksheet

Set wsCur = ActiveSheet
sCurCell = Selection.Address

On Error Resume Next
For Each WS In ThisWorkbook.Worksheets
    WS.Select
    WS.Range("A1").Select
Next WS
wsCur.Select
Range(sCurCell).Select
End Sub
 

egris52788

Board Regular
Joined
Mar 6, 2003
Messages
114
youre right but..........

youre right..i was trying to run it from my personal macro workbook...is there a way to make work on another workbook from my personal macro workbook?
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi,

Try this:
Code:
Sub ToTop()
Dim sCurCell As String
Dim WS As Worksheet, wsCur As Worksheet

Set wsCur = ActiveSheet
sCurCell = Selection.Address

On Error Resume Next
For Each WS In ActiveWorkbook.Worksheets
    WS.Select
    WS.Range("A1").Select
Next WS
wsCur.Select
Range(sCurCell).Select
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,804
Messages
5,638,450
Members
417,025
Latest member
MusterDuster

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top