Window Freeze

[Ronbo]

I have a spreadsheet that has titles in Columns A5:A55. Column A is frozen.
In Row 2 (B2:IV2) I have monthly dates B2 = 1/1/2008, B3 = 2/1/2008, B3
=3/1/2008, etc.

I am trying to create code in the sheet (sheet 4) that will open the sheet
and position the sheet to the current date, i.e. if today is 7-13-2008
opening sheet 4 will position the current month (7-1-2008) next to column A.
This would be in
column H.


Thanks for any Help!

Ronbo

 

[Ranjit kurian]

Hi Ronbo,

In the below code you can change the Range and Sheet name as you required,
and paste the code to a module it will work, if you want the macro to work
immediately when s/s is opened then paste the code as said, open your s/s
then do Alt + F11, after that paste the code to respective workbook/sheet

Sub Find_Date()
Dim FindString As Date
Dim Rng As Range
FindString = CLng(Date)
With Sheets("Sheet1").Range("B2:IV2")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End Sub

 

[Per Jessen]

Hi Ronbo

If I understand your requirements right, when sheet4 is activated, you want
to hide columns B:M, except the column containing the current date, and
select current date cell.

The code below is an event code, so it is to be copied into the code sheet
for sheet4.

Private Sub Worksheet_Activate()
TargetColumns = "B:M"
Columns(TargetColumns).Hidden = True
Columns(TargetColumns).Find(What:=Date, After:=Range("B1"),
LookAt:=xlWhole).Select
Columns(ActiveCell.Column).Hidden = False
End Sub

Best regards,
Per