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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,526
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,526

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,526

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,526
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,530
Messages
5,837,904
Members
430,520
Latest member
VenkateshRajaganesan

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