Select different cells in several sheets.

[PAB]

Good evening,

What is the correct way to do the below please.


Sub Test()
Sheets(Array("MEB", "MIB")).Range("V2").Select
Sheets(Array("MOB", "MUB")).Range("V8").Select
End Sub

Thanks in advance.

[mana]

What do you want to do after that?

[PAB]

Thanks for the reply.

I don't want to do anything after that.
It is just a case that when the Macro runs it puts the cursor in the relevant cells in the relevant Worksheets, it is so when I go to those particular sheets the cursor is ready and in the correct position, thats all.
I thought using an array or case statements would achieve this but didn't want to select the Worksheets because that is bad coding practice.

Thanks again.

[mdmackillop]

You could try

Application.Goto Sheet2.Range("C10")

[PAB]

Thanks for the reply mdmackillop,

That takes me to the actual sheet.
I want to be able to get the cursor into a certain cell within about a dozen different sheets within the same Workbook but without actually selecting those sheets.
I want the active sheet where the code is run from to stay the active sheet.
I don't know if this can actually be done without selecting the sheets first though.

Thanks in advance.

EDIT:
It is basically can I move the cursor to a particular cell in an inactive Worksheet without selecting the Worksheet first?

[PAB]

I have a button that when clicked runs the following...


Sub Reset()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With

Columns("M:AG").EntireColumn.Delete

With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

This button is on several sheets within the same Workbook.
Now when I press the button and the code runs, dependant on which Worksheet I am using I would like the cursor to end up in a particular cell.
Now out of the several Worksheets I am using there are three different cells I would like the cursor to end up.

For example:
In Sheet1, Sheet3, Sheet7, I would like the cursor to end up in cell S5.
In Sheet2, Sheet4, Sheet6, I would like the cursor to end up in cell B2.
In Sheet5, Sheet8, Sheet9, I would like the cursor to end up in cell C12.

I hope this makes it clearer.

Thanks in advance.

[PAB]

This works but is very messy...


Sub Resetx()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Columns("M:AG").EntireColumn.Delete

If ActiveSheet.Name = "Sheet1" Then Range("S5").Select
If ActiveSheet.Name = "Sheet2" Then Range("B2").Select
If ActiveSheet.Name = "Sheet3" Then Range("S5").Select
If ActiveSheet.Name = "Sheet4" Then Range("B2").Select
If ActiveSheet.Name = "Sheet5" Then Range("C12").Select
If ActiveSheet.Name = "Sheet6" Then Range("B2").Select
If ActiveSheet.Name = "Sheet7" Then Range("S5").Select
If ActiveSheet.Name = "Sheet8" Then Range("C12").Select
If ActiveSheet.Name = "Sheet9" Then Range("C12").Select

With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub


I also tried using the Application.Goto so I didn't use Select but without any success.

Thanks in advance.

EDIT:
Or perhaps use Select Case?

[PAB]

Has anyone got any ideas how the code could be more compact or efficient please?

[snb]

Sub M_snb()
Columns("M:AG").EntireColumn.Delete
application.goto range(choose(instr("137246589",right(activesheet.name,1))\3+1,"S5","B2","C12"))
End Sub

NB. Deleting columns is useless
Use userforms to guide the user's input: selecting cells should be redundant in well designed workbooks.
9 analogously designed sheets should be integrated into 1.