Changing the cell a workbook opens to (How to)

tholamew

New Member
Joined
Jan 20, 2005
Messages
6
I have a worksheet that I use to keep track of personal progress. Every column represents a new date in my personal progress. Every time that I open my file I would like 2 things to happen:

First, I want the spreadsheet to add new columns with dates for every day that has passed since last opening the file to the end of the dates already listed. (If it's been one day since last opening the file, it would only add one column with date......If 20 days have passed, I'd like it to add 20 columns with corresponding dates)

Second, as this list of dates may become large over time, I'd like to have the spreadsheet consistently open close to the current date. So, if we're consistently adding one new column for every day, the cell that the spreadsheet opens to should move over on a daily basis as well.

Big request. I'll be impressed if anyone can help out.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
Perhaps you'd prefer to insert rows? You only have 256 columns to work with.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I agree with Taz, but how about something lke this (can be adjusted easily to rows):
Press Alt-F11
In the left pane, double-click on "ThisWorkbook"
In the right pane, paste this in:
Private Sub Workbook_Open()
Range("A1").End(xlToRight).Select
Do Until Format(ActiveCell.Value, "dd/mm") >= Format(Now(), "dd/mm")
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = ActiveCell.Offset(0, -1).Value + 1
Loop
End Sub

Is that what you are after?
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
Also, being American, you probably want to change my date syntax from "dd/mm" to "mm/dd".
 

Forum statistics

Threads
1,148,222
Messages
5,745,467
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top