Pages

Sunday, March 11, 2012

Navigate through all the worksheet and Press Ctrl + Home . Goto first cell in each worksheet

If you want to select the first cell after freeze pane on each worksheet and save it. So that when user opens the workbook he or she do not need to press CTRL+ Home in each worksheet to goto first cell.

Here is the code(You can add it to workbook open event / before close event )-


Sub goto_first_cell_in_each_worksheet()
    Dim wk As Worksheet
    For Each wk In ThisWorkbook.Worksheets
      If wk.Visible = xlSheetVisible Then
      wk.Select
        If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn = 0 Then
          Application.Goto Range("a1")
        Else
          Application.Goto Range(Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Address)
        End If
      End If
    Next
    ActiveWorkbook.Save
End Sub

No comments:

Post a Comment