• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Navigate through all the worksheet and Press Ctrl + Home Using VBA

A

ashish koul

Member
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/she do not have to press CTRL+ Home in each worksheet to go to first cell.

Here is the code -

[pre]
Code: 
Sub goto_first_cell_in_each_worksheet()
Dim wk As Worksheet
For Each wk In ThisWorkbook.Sheets
wk.Select
If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn = 0 Then
Application.Goto Range("a1")
Exit Sub
Else
Application.Goto Range(Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Address)
End If
Next
ActiveWorkbook.Save
End Sub
[/pre]
 
wk.Select error


run time error "1004":


Method 'Select of Object'_worksheet failed.


any advise please ?
 
Hi Ashish ,


I think a chart sheet can give rise to an error. This might be the way to go :

[pre]
Code: 
Sub goto_first_cell_in_each_worksheet()
Dim wk As Variant
For Each wk In ThisWorkbook.Sheets
If wk.Type = xlWorksheet Then
wk.Select
If ActiveWindow.SplitRow = 0 And ActiveWindow.SplitColumn = 0 Then
Application.Goto Range("a1")
Exit Sub
Else
Application.Goto Range(Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Address)
End If
End If
Next
ActiveWorkbook.Save
End Sub
Even shorter is this :

Sub goto_first_cell_in_each_worksheet()
Dim wk As Variant
For Each wk In ThisWorkbook.Sheets
If wk.Type = xlWorksheet Then
wk.Select
ActiveWindow.VisibleRange.Cells(1, 1).Select
End If
Next
ActiveWorkbook.Save
End Sub
[/pre]
Narayan
 
Also Hidden and VeryHidden sheets can give this error.


See this recent thread on MrExcel:

http://www.mrexcel.com/forum/excel-questions/665227-1004-select-method-worksheet-class-failed.html
 
@NARAYANK991 ,shrivallabha ---- Thanks I missed that part :(


@ianb - Try this updated one

[pre]
Code: 
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
[/pre]
 
Hi Ashish ,


I think it can do with more updates.


I missed it in my earlier post , but why the Exit Sub ?


This seems to be OK :

[pre]
Code: 
Sub goto_first_cell_in_each_worksheet()
Dim wk As Variant
For Each wk In ThisWorkbook.Sheets
If wk.Type = xlWorksheet And wk.Visible = xlSheetVisible Then
wk.Select
ActiveWindow.VisibleRange.Cells(1, 1).Select
End If
Next
ActiveWorkbook.Save
End Sub
[/pre]
Narayan
 
Hi Ashish


You can edit your post using with {edit} button in the bottom of posts which is indicating the time of post


Note: the edit option will allow us with in 10mts


Thanks


SP
 
Hi Ashish ,


Sorry for the earlier posted code ; VisibleRange does not do the job. The following does :

[pre]
Code: 
Sub goto_first_cell_in_each_worksheet()
Dim wk As Variant
For Each wk In ThisWorkbook.Sheets
If wk.Type = xlWorksheet And wk.Visible = xlSheetVisible Then
wk.Select
Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
End If
Next
ActiveWorkbook.Save
End Sub
[/pre]
The above works even when the panes are not frozen i.e. a separate test for SplitRow / SplitColumn = 0 is not required.


Narayan
 
Hi All Ashish advise is good one line chnage and it works for me.


Sub goto_first_cell_in_each_worksheet()

Dim wk As Variant

For Each wk In ThisWorkbook.Sheets

If wk.Type = xlWorksheet Then

wk.Activate ' Line Change

ActiveWindow.VisibleRange.Cells(1, 1).Select

End If

Next

ActiveWorkbook.Save

End Sub
 
Hi Ashish ,


My apologies for continuing to stretch this topic !


It all started off with a very straightforward VBA procedure to replace the familiar CTRL HOME combo , which takes the cursor to the first available cell in a worksheet.


It has expanded to include hidden and veryhidden worksheets ( thanks to Shrivallabha ) ; in the process , I think we need to make it more comprehensive , so that it becomes a real VBA replacement for the keyboard shortcut.


I tried having some rows and columns hidden , and the current procedure does not take this into account ; hence this latest update.

[pre]
Code: 
Sub goto_first_cell_in_each_worksheet()
Dim wk As Variant
For Each wk In ThisWorkbook.Sheets
If wk.Type = xlWorksheet And wk.Visible = xlSheetVisible Then
wk.Select
Cells(ActiveWindow.SplitRow + 1, ActiveWindow.SplitColumn + 1).Select
i = 0
j = 0
Do
If ActiveCell.Offset(i, j).EntireRow.Hidden Then i = i + 1
If ActiveCell.Offset(i, j).EntireColumn.Hidden Then j = j + 1
Loop Until Not (ActiveCell.Offset(i, j).EntireRow.Hidden) And Not (ActiveCell.Offset(i, j).EntireColumn.Hidden)
ActiveCell.Offset(i, j).Select
End If
Next
ActiveWorkbook.Save
End Sub
[/pre]
I had earlier posted that VisibleRange does not work ; I had not explained in more detail. Consider when the home cell is not on the visible screen e.g. suppose the cursor is on AE390 ; if you now use VisibleRange , it will take you to L366 , since that is the first available cell in the visible range. Again , this will depend on the row heights and column widths ; the cursor can end up anywhere that is the top left corner of the displayed screen.


Narayan
 
You must log in or register to reply here.
Back
Top