Macro to select A1 in all sheets so that users always at top of sh

[Trish Smith]

Hi everybody,

I'm hoping that someone can show me how to select a1 on each sheet of a
workbook so that all users don't have to scroll up when they re-open
worksheet.

Thanks for your help

 

[Mike H]

Hi,

Alt+F11 to open VB editor. Double click 'This Workbook' and paste this nin
on the right

Private Sub Workbook_Open()
For X = 1 To Worksheets.Count
Sheets(X).Select
Sheets(X).Range("A1").Activate
Next
Sheets(1).Select
End Sub


Mike

 

[Bob Phillips]

Private Sub Workbook_Open()
Dim this As Worksheet
Dim sh As Worksheet

Set this = ActiveSheet
For Each sh In ThisWorkbook.Worksheets
sh.Activate
Range("A1").Select
Next sh
this.Activate
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[Trish Smith]

Hi Mike,

That worked fine! Thank you!

Could you explain what I'm actually doing when I dim X as long - not at all
sure if that's what you would have done.

Thank you

 

[Trish Smith]

Thank you Bob,

That's brilliant!

Cheers

 

[Mike H]

Hi,

Glad I could help.

Dim X as long

This dimensions the variable X as an integer in the range -2,147,483,648 to
2,147,483,647.

Yes I should have dimensioned the variable but would have used 'Integer'
which allows values in the range -32,768 to 32,767


Mike

 

[Trish Smith]

Thanks again Mike,

Is that because the computer has to use the memory that the variable could
potentially take up? I'm showing my ignorance here but at least I'm learning


Cheers

 

[Mike H]

Trish,

Any variable not specifically dimensioned is of type Variant and these can
take up to 22 bytes of memory to store. A long or Integer cariable takes only
2 bytes so is more economic on memory.

In choosing a data type you need to think about what you want the variable
to be. For example type

Long -2,147,483,648 to 2,147,483,647
Integer -32,768 to 32,767
Byte 0 to 255

Consider now
Dim X as Long
x=5.8
Because X isn't an integer Excel rounds it to 6 but no error is generated
X=40000
produces an error because it is outside of the allowable value range

Have a look here
http://www.ozgrid.com/VBA/variables.htm

Mike

 

[Trish Smith]

Ahhhh! The penny drops.

Many thanks for the link as well Mike.

Cheers

 

[Mike H]

I meant

dim x as integer
X=40000
produces an error because it is outside of the allowable value range


Mike