| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formatting workbook and going to first sheet
Hi All
Fairly straightforward question I think, basically I want a little macro to format a workbook I have open. It will select "A1" in each sheet and then return to the first sheet. This is what I have: Dim i As Integer wksname = ActiveSheet.Name For i = 1 To Sheets.Count Sheets(i).Select Range("A1").Select Next i 'Sheets(wksname).Activate unfortunately I get a "run time error 1004" message saying "Select method of workclass failed". Can anyone shed any light? Cheers in advance |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting workbook and going to first sheet
Hi,
This will select A1 of each sheet but you don't say what you want to do. In any case it's highly inlikely you need to select the cell, you can format it without selecting This goes in a general module Sub Dont_Select() Dim i As Integer wksname = Sheets(1).Name For i = 1 To Worksheets.Count Sheets(i).Select Range("A1").Select Next i Sheets(wksname).Activate End Sub Mike "Stav19" wrote: Hi All Fairly straightforward question I think, basically I want a little macro to format a workbook I have open. It will select "A1" in each sheet and then return to the first sheet. This is what I have: Dim i As Integer wksname = ActiveSheet.Name For i = 1 To Sheets.Count Sheets(i).Select Range("A1").Select Next i 'Sheets(wksname).Activate unfortunately I get a "run time error 1004" message saying "Select method of workclass failed". Can anyone shed any light? Cheers in advance |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting workbook and going to first sheet
Careful - this may fail if there are any Chart or Macro sheets in the
workbook. Try Worksheets(i).Select instead of Sheets(i).Select In article , Mike H wrote: Hi, This will select A1 of each sheet but you don't say what you want to do. In any case it's highly inlikely you need to select the cell, you can format it without selecting This goes in a general module Sub Dont_Select() Dim i As Integer wksname = Sheets(1).Name For i = 1 To Worksheets.Count Sheets(i).Select Range("A1").Select Next i Sheets(wksname).Activate End Sub Mike "Stav19" wrote: Hi All Fairly straightforward question I think, basically I want a little macro to format a workbook I have open. It will select "A1" in each sheet and then return to the first sheet. This is what I have: Dim i As Integer wksname = ActiveSheet.Name For i = 1 To Sheets.Count Sheets(i).Select Range("A1").Select Next i 'Sheets(wksname).Activate unfortunately I get a "run time error 1004" message saying "Select method of workclass failed". Can anyone shed any light? Cheers in advance |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting workbook and going to first sheet
On Jan 19, 10:27*pm, JE McGimpsey wrote:
Careful - this may fail if there are any Chart or Macro sheets in the workbook. Try * *Worksheets(i).Select instead of * *Sheets(i).Select In article , *Mike H wrote: Hi, This will select A1 of each sheet but you don't say what you want to do.. In any case it's highly inlikely you need to select the cell, you can format it without selecting This goes in a general module Sub Dont_Select() Dim i As Integer wksname = Sheets(1).Name For i = 1 To Worksheets.Count Sheets(i).Select Range("A1").Select Next i Sheets(wksname).Activate End Sub Mike "Stav19" wrote: Hi All Fairly straightforward question I think, basically I want a little macro to format a workbook I have open. *It will select "A1" in each sheet and then return to the first sheet. *This is what I have: Dim i As Integer wksname = ActiveSheet.Name For i = 1 To Sheets.Count Sheets(i).Select Range("A1").Select Next i 'Sheets(wksname).Activate unfortunately I get a "run time error 1004" message saying "Select method of workclass failed". Can anyone shed any light? Cheers in advance- Hide quoted text - - Show quoted text - cheers for your help, I'll have to give that a go!!! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Formatting workbook and going to first sheet
On Jan 19, 10:56*pm, Stav19 wrote:
On Jan 19, 10:27*pm, JE McGimpsey wrote: Careful - this may fail if there are any Chart or Macro sheets in the workbook. Try * *Worksheets(i).Select instead of * *Sheets(i).Select In article , *Mike H wrote: Hi, This will select A1 of each sheet but you don't say what you want to do. In any case it's highly inlikely you need to select the cell, you can format it without selecting This goes in a general module Sub Dont_Select() Dim i As Integer wksname = Sheets(1).Name For i = 1 To Worksheets.Count Sheets(i).Select Range("A1").Select Next i Sheets(wksname).Activate End Sub Mike "Stav19" wrote: Hi All Fairly straightforward question I think, basically I want a little macro to format a workbook I have open. *It will select "A1" in each sheet and then return to the first sheet. *This is what I have: Dim i As Integer wksname = ActiveSheet.Name For i = 1 To Sheets.Count Sheets(i).Select Range("A1").Select Next i 'Sheets(wksname).Activate unfortunately I get a "run time error 1004" message saying "Select method of workclass failed". Can anyone shed any light? Cheers in advance- Hide quoted text - - Show quoted text - cheers for your help, I'll have to give that a go!!!- Hide quoted text - - Show quoted text - I tried it and it worked a treat with the "Worksheets" for "Sheets" change, thanks for your help! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Color formatting issue when copying excel sheet from workbook to o | Excel Discussion (Misc queries) | |||
| Copy Excel Sheet to another sheet and preserve formatting? | Excel Programming | |||
| Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
| How do I copy formatting from 1 sheet to next in same workbook? | Excel Worksheet Functions | |||
Hybrid Mode
