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
        If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn = 0 Then
          Application.Goto Range("a1")
          Application.Goto Range(Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Address)
        End If
      End If
End Sub

No comments:

Post a Comment

Import data from SQL

Macro to import data from SQL using ADO connection string: Sub Import_data_from_SQL() ' Tools -> References -> Microsoft Active...