| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to select A1 in all sheets so that users always at top of sh
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 -- Trish |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top of sh
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 "Trish Smith" wrote: 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 -- Trish |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top of sh
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 -- __________________________________ HTH Bob "Trish Smith" wrote in message ... 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 -- Trish |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
Thank you Bob,
That's brilliant! Cheers -- Trish "Bob Phillips" wrote: 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 -- __________________________________ HTH Bob "Trish Smith" wrote in message ... 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 -- Trish |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
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" wrote: 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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
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 -- Trish "Mike H" wrote: 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" wrote: 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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
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" wrote: 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 -- Trish "Mike H" wrote: 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" wrote: 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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
Ahhhh! The penny drops.
Many thanks for the link as well Mike. Cheers -- Trish "Mike H" wrote: 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" wrote: 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 -- Trish "Mike H" wrote: 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" wrote: 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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to select A1 in all sheets so that users always at top o
I meant
dim x as integer X=40000 produces an error because it is outside of the allowable value range Mike "Trish Smith" wrote: Ahhhh! The penny drops. Many thanks for the link as well Mike. Cheers -- Trish "Mike H" wrote: 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" wrote: 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 -- Trish "Mike H" wrote: 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" wrote: 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 "Mike H" wrote: 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 "Trish Smith" wrote: 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 -- Trish |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to select all sheets | Excel Discussion (Misc queries) | |||
| macro to select all sheets | Excel Discussion (Misc queries) | |||
| Macro to select and print sheets | Excel Discussion (Misc queries) | |||
| macro to select sheets | Excel Programming | |||
| Select Sheets via Array Macro | Excel Programming | |||
Linear Mode
