Sheet stuff

[DUNDERHEAD]

I have 18 sheets in a book. How do i make all sheets go to the same cell on there own sheet just by clicking on any sheet any cell?

 

[rilzniak]

If I understand correctly, highlight all sheets and then click on a particular cell and each sheet will move the cursor to that cell.

 

[Fishboy]

Hi DUNDERHEAD,

For a slightly more automated solution you could try the following macro. Simply insert a button onto your main sheet and apply the following code to it. When you click the macro button you will be asked to input a cell value. This can either be done by typing the desired cell in the box or by clicking the desired cell, then pressing OK. Once this is done the macro will select all sheets and select the specified cell on them all, before re-selecting the main sheet where the button was:

Sub SelectSameCellOnAllSheets()
' Defines variables
Dim Rng As Range


On Error Resume Next


' Sets Rng as the cell selected for the input box
Set Rng = Application.InputBox( _
    "Please select desired range", _
    "Select Range", , Type:=8)


' If Rng value is nothing then...
If Rng Is Nothing Then
    ' Display an error to that effect
    MsgBox "No cell was selected.", vbOKOnly, "Attention!"
    ' Exit the macro early
    Exit Sub
' Else if Rng is not nothing then...
Else
    ' Select all sheets
    ActiveWorkbook.Sheets.Select
    ' Select Rng
    Rng.Select
    ' Re-select the main active sheet
    ActiveSheet.Select
End If


End Sub