Macro to place all Sheets to start in Range A1

[ortizSr]

I have been expermenting with the following macro but without success, as it simply places only the current Sheet in A1.

Sub FirstCellAllSheets()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
Range("A1").Select
Next wsSheet

End Sub

The purpose of this macro is to add it to every Macro in a workbook, as some of the macros operate in relative mode.

I use Excel 2000 SR-1

 

[Egress1]

If you have less worksheets than macros you might be better off just using the worksheet activate events for each sheet and doing the [A1].Select in each.

Just a thought.

 

[colinharwood]

Hi

Try this

Sub RangeA1Select()

Dim x As Integer
For x = 1 To Number of sheets in workbook
Sheets(x).Activate
Range("A1").Select
Next

End Sub

Colin

 

[ortizSr]

Thanks to both Colinharwood, and Egress1:

First to Colinharwood:

The following produced a syntax error. Any suggestions??

For x = 1 To Number of sheets in workbook


To Egress1:

The Workbook has over 20 sheets, and almost each has vital data that I wish to preserve.

I created a macro that addresses each of these sheets, adn places the pointer to cell A1, but is very lengthly, and if I change any name, or add more sheets, Updating becomes a task.

 

[ortizSr]

To Colinharwood:

I apologize for not examing closely your initial suggestion.

I changed the line to indicate how many sheets and it worked like a charm. See Below.


Thank you very much.

Case Closed.


Sub RangeA1Select()

Dim x As Integer
For x = 1 To 20
Sheets(x).Activate
Range("A1").Select
Next

End Sub

 

[colinharwood]

Hi

I've been playing with your original code, try this

Sub FirstCellAllSheets()
Dim wsSheet As Worksheet

For Each wsSheet In Worksheets
wsSheet.select
Range("A1").Select
Next wsSheet

End Sub

With my last code did you substitute the number of your last worksheet in the line
for x=1 to Number of sheets in workbook

eg

for x=1 to 20

Colin

 

[Richie(UK)]

Hi,

Your original code was nearly there, you just needed to select the relevant sheet first. Like this:

Sub FirstCellAllSheets()
Dim wsSheet As Worksheet

Application.ScreenUpdating = False
For Each wsSheet In Worksheets
    With wsSheet
        .Select
        .Range("A1").Select
    End With
Next wsSheet
Application.ScreenUpdating = True

End Sub

The screenupdating code prevents any screen flicker from flipping between the various sheets.

Also, remember that it is rarely necessary to use either Select or Activate to work with objects. Could you elaborate on the purpose of this code - I didn't quite follow your original explanation.

 

[ortizSr]

To both Colinharwood, and Richie(UK):

Thanks for the fast and excellents responses.

I tried both suggested codes, and they worked better than expected, as the first go around would have required that I update the macro each time I had workbooks with greater than 20 sheets.

To Richie(UK):

I quote your statement:

Also, remember that it is rarely necessary to use either Select or Activate to work with objects. Could you elaborate on the purpose of this code - I didn't quite follow your original explanation.

I do not understand the first part of this quote. What do you mean by it??

The Purpose for this macro is to avoid scrambled data with some of the macros that function in a relative mode.

 

[Richie(UK)]

Hi,

Consider the following two simplistic examples:

Sub test1()
Worksheets("Sheet3").Select
Range("A10").Activate
ActiveCell.Value = 123
End Sub

Sub test2()
Worksheets("Sheet3").Range("A10").Value = 123
End Sub

Both routines achieve the same objective, however the second routine achieves it without needing to Select or Activate the sheet or range objects. Generally this approach will make your code shorter and easier to read and, more importantly, faster to execute.

Could you provide an example of the macros that you are using that necessitate this approach? We may be able to help you to amend them to take into account the approach outlined above.

 

[ortizSr]

To Richie(UK):

You guys are great, and extremely helpful.

I will review the many macros I have on the project I am currently developing (first one), and will submit one that uses the Select, and Activate through-out to you.

Do I do this via MrExcel, or a Private E-mail??

Thanks again. I am thrilled to be learning so much from this forum.