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

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

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

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!!!

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!