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

 

[Richie(UK)]

Hi ortizSr,

Please post your code to this forum, and on this same thread. This has two advantages:

1. For you - I may not be available when you post your code but there are many very talented and very helpful people on this board that may be able to help in my absence. Therefore, you will get a quicker response to your query.

2. For others - there may be, now or at a later date, other people with similar queries to you. If all queries and responses are posted to the forum then the information contained may be of benefit to these other people.

I'm glad that you are learning from the information available here :D . Good luck with your project.

 

[ortizSr]

Dear Richie(UK):

Thank you for the response, and I will post the macro to this forum.

 

[just_jon]

perhaps --

Sub RangeA1Select() 

Dim x As Integer 

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculateManual

On Error goto AbEnd

For x=1 to Worksheets.Count
    Sheets(x).Activate 
    Range("A1").Select 
Next x

AbEnd:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[ortizSr]

To Just_jon:

Thanks for the routine, all the suggestions given so far work very well. I haven't received as much help on my other posted questions as for this one.

I just had to correct a syntax error in the following line of the code:

Application.Calculation = xlCalculationManual You had Calculate.

Thanks again.

Now I must try to gain an understanding of what each step of the code does in all the 3 suggested Codes, as I am a beginner with VBA.

 

[just_jon]

Sorry about the typo, sir. Good catch!

 

[ortizSr]

To: Richie(UK)

Here is a sample of the many macros I use in the project, for the reduction of the "Select &/or Activate" coding.

It purpose is to provide the next suffix to an Invoice number consisting of the (Month-Year-Next Record No).

May I further add that I had to resort to a Mickey-mouse technique in setting the first record no in the Database. I did not know how to avoid going to the bottom of the Worksheet when the database is empty.

How can I modify this, so that I could start with any number, and not '0001??

Sub NextRecordNo()
'Summary: To Set a Record No for each Invoice in the
' Invoice Database, and to Set the Suffix for the
' Next Invoice Number

'Step 1 is to position all sheets to A1
Call ToFirstCellAllSheets

'Step 2 To insure that the first Record No is in place.
Sheets("Invoice Database").Select
Selection.End(xlToRight).Select
Range("HP2").Select
ActiveCell.FormulaR1C1 = "'0001"
Range("A1").Select

'Step 3 To create the Next Record No in
' the Invoice Database,
Sheets("Invoice Database").Select
Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
ActiveCell.Range("A1:A2").Select
Selection.DataSeries Rowcol:=xlColumns, _
Type:=xlAutoFill, Date:=xlDay, _
Trend:=False
Selection.End(xlDown).Select
Selection.Copy

'Step 4 Post new Record No to the Invoice Master
' Cell N4
Sheets("Invoice Master").Select
ActiveCell.Offset(2, 13).Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Invoice Database").Select
Application.CutCopyMode = False
Range("A1").Select
End Sub