ALL WORKSHEETS SCROLLED TO TOP AND CELL A1 ACTIVE

[egris52788]

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.

 

[al_b_cnu]

Hi,

How about this:

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]

GOT ERROR MESSAGE

hi alan, i get this error message:

METHOD SELECT OF OBJECT WORKSHEET FAILED

 

[egris52788]

i think its my hidden sheets that are causing the error. how do i get it to avoid hidden sheets?

 

[al_b_cnu]

Hi,

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

 

[egris52788]

DOESNT WORK

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

 

[al_b_cnu]

Oh yes it does :¬)

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]

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]

Hi,

Try this:

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