Good evening,
What is the correct way to do the below please.
Thanks in advance.Sub Test() Sheets(Array("MEB", "MIB")).Range("V2").Select Sheets(Array("MOB", "MUB")).Range("V8").Select End Sub
Good evening,
What is the correct way to do the below please.
Thanks in advance.Sub Test() Sheets(Array("MEB", "MIB")).Range("V2").Select Sheets(Array("MOB", "MUB")).Range("V8").Select End Sub
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
What do you want to do after that?
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.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
You could try
Application.Goto Sheet2.Range("C10")
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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?
Last edited by PAB; 03-26-2017 at 03:59 AM.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
I have a button that when clicked runs the following...
This button is on several sheets within the same Workbook.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
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.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
This works but is very messy...
I also tried using the Application.Goto so I didn't use Select but without any success.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
Thanks in advance.
EDIT:
Or perhaps use Select Case?
Last edited by PAB; 03-26-2017 at 06:06 AM.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
Has anyone got any ideas how the code could be more compact or efficient please?
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.
-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
NB. Deleting columns is uselessSub M_snb() Columns("M:AG").EntireColumn.Delete application.goto range(choose(instr("137246589",right(activesheet.name,1))\3+1,"S5","B2","C12")) End Sub
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.