VBA Select Cell On Multiple Sheets

[julia55]

I am trying to have an input box prompt for a cell number, and then select that cell on all of the sheets in my workbook. The sheet names and number of sheets will vary each time. This is what I have so far, but of course it is making me enter the cell number multiple times for each sheet and I would rather only have to enter it once.

Sub SelectCells()
Application.ScreenUpdating = False
 
Dim i As Integer
Dim currentsheet As Worksheet
For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
Dim MyRange As Range
Set MyRange = Application.InputBox(Prompt:= _
                "Please enter the cell number you would like to select.", _
                    Title:="SPECIFY CELL", Type:=8)
MyRange.Select
Next i
Application.ScreenUpdating = True
Sheets("Total IX Series").Select
 
End Sub

 

[ColinKJ]

Hi julia55,

The reason you are being asked for the cell details for each sheets is because you've put the input in the middle of the For / Next Try this

Sub SelectCells()
Application.ScreenUpdating = False
 
Dim i As Integer
Dim currentsheet As Worksheet
Dim MyRange As Range
Set MyRange = Application.InputBox(Prompt:= _
                "Please enter the cell number you would like to select.", _
                    Title:="SPECIFY CELL", Type:=8)
 
For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
MyRange.Select
Next i
Application.ScreenUpdating = True
Sheets("Total IX Series").Select
 
End Sub

ColinKJ

 

[julia55]

I tired that, but got an error.. "Run-Time error '1004': Select method of Range class failed."

 

[ColinKJ]

Hi julia55,

Your code hasn't any checks to ensure that what is entered in the Inputbox is a valid Cell Reference.

What are you entering ?

ColinKJ

 

[julia55]

I enter Z14 for an example and that's when I got the error message. When I go to debug it highlights the line that states:

MyRange.Select

 

[julia55]

Also, it is working on the active sheet, but not the remaining sheets.

 

[ColinKJ]

Hi julia55,

This works in a test WB I just did.

Sub SelectCells()
Application.ScreenUpdating = False
 
Dim i As Integer
Dim currentsheet As Worksheet
Dim MyRange As Range
X = InputBox("Enter Cell Ref")
 
For i = 1 To ActiveWorkbook.Worksheets.Count
Set currentsheet = ActiveWorkbook.Worksheets(i)
Worksheets(i).Activate
Range(X).Select
Next i
Application.ScreenUpdating = True
Sheets("Total IX Series").Select

Colin

 

[julia55]

That worked out!!! Thanks so much for the help!!!!