Formatting workbook and going to first sheet

[Stav19]

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

 

[Mike H]

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

 

[JE McGimpsey]

Careful - this may fail if there are any Chart or Macro sheets in the
workbook.

Try

Worksheets(i).Select

instead of

Sheets(i).Select

 

[Stav19]

Careful - this may fail if there are any Chart or Macro sheets in the
workbook.

Try

   Worksheets(i).Select

instead of

   Sheets(i).Select






- Show quoted text -

cheers for your help, I'll have to give that a go!!!

 

[Stav19]

Careful - this may fail if there are any Chart or Macro sheets in the
workbook.

   Worksheets(i).Select

instead of

   Sheets(i).Select


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!