Automatically inserting a column

T

Tara

I have a spreadsheet that has several columns with dates - Today's date,
yesterday's date, etc. If a new date is typed in, how can I set the
spreadsheet to automatically insert a column for the new date?
 
G

Gord Dibben

Tara

To do what you want would require VBA code of some sort.

Where/how would the new date be typed in?

Where would the new column be inserted?

Why not just insert a new column and type the date in that column?


Gord Dibben MS Excel MVP
 
T

Tara

Gord,
One of the managers brought this to my attention. He is currently inserting
the column & date manually but I believe there is a lot of data input each
day and he would like to be able to just type the date in any cell and it
would insert automatically. It is my understanding it would go just to the
right of the main date cell. I hope this helps.
Tara
 
G

Gord Dibben

What would insert automatically?

The column?

Type the date in any cell and that date would stay there?

Or go into the new column?

Into which cell in the new column?

What is the "main date cell"?

I'm struggling here as you can see.

Can we give him a macro which he can trigger with a button or shortcut key to
insert a new column and enter the date?

Sub Insert_Enter()
Selection.EntireColumn.Insert
ActiveCell.Value = Format(Now, "dd mmm yyyy")
End Sub

This will insert a column and enter in the active cell.


Gord Dibben MS Excel MVP
 
T

Tara

Hi Gord,

I am going to need to verify with him, and have him write you back. As far
as I can tell, he has a spreadsheet that has 40 rows (1 for each employee)
and each row has several (20+) columns with dates of training classes
completed (ex. 8/17/06, 8/17/05,8/17/04,8/17/03 etc.) As an employee takes a
class they currently insert a "cell" into the spreadsheet manually and type
in the new date. He wants the spreadsheet to insert a new cell and new date
and only effect 1 row at a time. Sometimes they have several at a time to
update. (will a macro have to be set up for each row?)

He also has a conditional formatting in the "Current Date" column that turns
red when the training is outdated. He wants to have the existing formatting
in that column to move to the new column automatically. I know this is
confusing, I am sorry. Maybe I am not explaining it right.
Thank you for all your help.
Tara
 
G

Gord Dibben

Should read....

This will insert a column and enter today's date in the active cell.


Gord

What would insert automatically?

The column?

Type the date in any cell and that date would stay there?

Or go into the new column?

Into which cell in the new column?

What is the "main date cell"?

I'm struggling here as you can see.

Can we give him a macro which he can trigger with a button or shortcut key to
insert a new column and enter the date?

Sub Insert_Enter()
Selection.EntireColumn.Insert
ActiveCell.Value = Format(Now, "dd mmm yyyy")
End Sub

This will insert a column and enter in the active cell.


Gord Dibben MS Excel MVP
Click to expand...

Gord Dibben MS Excel MVP
 
G

Gord Dibben

OK, so it's not a column he wants inserted, it is a cell inserted in a row.

Revised macro.........

Sub Insert_Enter()
Selection.Insert Shift:=xlToRight
ActiveCell.Value = Format(Now, "dd mmm yyyy")
Selection.Columns.AutoFit
End Sub

Select any cell in any row and run the macro.

Cells will be shifted to the right and date will be entered in the activecell.

No, you don't need a macro for each row.

Selection and ActiveCell cover all rows and any cell.


Gord

Hi Gord,

I am going to need to verify with him, and have him write you back. As far
as I can tell, he has a spreadsheet that has 40 rows (1 for each employee)
and each row has several (20+) columns with dates of training classes
completed (ex. 8/17/06, 8/17/05,8/17/04,8/17/03 etc.) As an employee takes a
class they currently insert a "cell" into the spreadsheet manually and type
in the new date. He wants the spreadsheet to insert a new cell and new date
and only effect 1 row at a time. Sometimes they have several at a time to
update. (will a macro have to be set up for each row?)

He also has a conditional formatting in the "Current Date" column that turns
red when the training is outdated. He wants to have the existing formatting
in that column to move to the new column automatically. I know this is
confusing, I am sorry. Maybe I am not explaining it right.
Thank you for all your help.
Tara
Click to expand...

Gord Dibben MS Excel MVP
 
Top