Automatically run macro when cell changes

[delpiero5464]

Hello,

I am very new to VBA, and could use some help.

I have created a macro (through recording) to copy a column of dates (calculated off a formula) and paste values in the column to its right. I would like to have the macro run every time a blank cell changes to a calculated date. Is there a way to do this, and what would be the code?

Remember I am very new to VBA, and ANY help would be much appreciated.

Thanks!

[HaHoBe]

Hi, delpiero5464,

sound as you may work with teh Worksheet_Change-Event behind the worksheet you are filling in data.

Rightclick on the worksheet-tab, choose View Code, change to the code window, and paste this code as an example which would check column C for any entry. A MessageBox is displayed but you may change that line of code to call your recorded macro. The first code may look line

Please Login or Register  to view this content.

As I expect the macro recorder to have worked with ActiveCell you may need to cross the address of the changed cell across to your macro. This sample calls the macro and colours the cells in Columns E to J in yellow

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Ciao,
Holger

[delpiero5464]

Thanks for the quick response!

I'm not sure if this will fulfill what I am needing. I would like a macro that: every time a date is populated in a cell in column "P", the date will paste value in the corresponding cell in column "P". Is it possible to do this with a Worksheet_Change-Event?

Here is the current code I have:
Sub Macro1()
'
' Macro1 Macro
' Paste Values
'
' Keyboard Shortcut: Ctrl+l
'
Range("P2:P20").Select
Selection.Copy
Range("Q2:Q20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub

[HaHoBe]

Hi, delpiero5464,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

[delpiero5464]

That helps, but it pastes today's date in column "Q" (regardless of what date is entered into column "P"). I want it to paste whatever date is in column "P", into column "Q". Any thoughts?

[HaHoBe]

Hi, delpiero5464,

Please Login or Register  to view this content.

Ciao,
Holger

[delpiero5464]

Thanks, its getting there. However, the macro will not copy and paste values on cells that have calculated formulas. For example the cell P2 has the formula =Today()+N2. The macro will not paste the date in column "Q". I need the macro to paste dates of this calculated cell. Is it possible?

[HaHoBe]

Hi, delpiero5464,

could you plesae explain

I would like to have the macro run every time a blank cell changes to a calculated date.

Are you talking about one cell or about copying/autofilling cells? And what about the dates being pasted: overwrite values in Q if there are any or add to the right of these? Maybe it would be best you attach a workbook and explain what you have and what you want to result to be like in two sceanrios.

Yes, itīs possible via Worksheet_Calculate but I would like to narrow the range down instead of having any formula in the sheet firing the event. And right now I donīt believe that we would need that event but could work with the Workbook_Open event for any formula instead.

Ciao,
Holger

[delpiero5464]

Hopefully I can explain more clearly with the help of the spreadsheet attached (hopefully I did it right).

An action item is selected in column A. An action date is then calculated in column D. I want the date in column D to then be pasted as a value (so that it cannot change with today's date). It can either paste over the previous formula in column D or paste next to it in column E.

I know it is as simple as copying and pasting values at the end of each day, but I work with a staff who does not do well with excel and I want to automate this as much as possible. They all work on different lists like this, and I need to know what day the action was taken(ie. item in column A was selected).

Hope that makes sense. Thanks for all your help so far. Hope we can solve this soon!

[HaHoBe]

Hi, delpiero5464,

regarding the date I wouldnīt even start with filling in a formula in Column D. I havenīt used Offset here but worked with the columns as you mentioned P and Q in the other posts.

Any change in Column A should write the date into column D while any change in Column B should fill or empty the value in column C.

Please Login or Register  to view this content.

Ciao,
Holger

[delpiero5464]

This is awesome! Thanks so much!

To help me better understand the code and learn more, can you explain in simpler terms what the code language means and what it does?

[HaHoBe]

Hi, delpiero5464,

the event will only be started if any entry is made into a cell (so no formulas will trigger that). It will only work on the sheet that this code is behind (you could switch that to work for all worksheets in a workbook by placing Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) in ThisWorkbook).

If more than one cell at a time is changed the code wonīt execute as that would raise an error. As Row 1 holds the headers any execution of the code should start with at least row 2, here it is from row 3 on. If that is true the column in which the change takes place is checked. 1 stands for A, 2 for B and so on.

The change is made in Column A so we enter the date in Column D but leave the other columns on their own and wonīt do anything. If anything is entered or changed in Column B Column C will be calculated as the date form Column D and the number of days from Column B. If you delete the value in Column B the value from Column C is replaced by nothing.

Thatīs all. I could have used If-Statements to check out the number of the Columns but I prefer to use a Select Case-Statement which sort of makes it easier to understand what cases must be fulfilled to run the adequte code.

HTH,
Holger

[delpiero5464]

I have tried inputting the code into my actual spreasheet now and I cannot figure out why it will not work correctly. In my actual spreadsheet, I had to change some of the column references, but it still does not work. Can you help? In my actual spreadsheet here are the column differences(new=former), column N = column A, column P = column B, column Q = column C, column D = column O

[HaHoBe]

Hi, delpiero5464,

did you adjust the columns to be checked?

Please Login or Register  to view this content.

If this code wonīt run you maybe attach another copy of your workbook.

Ciao,
Holger

[delpiero5464]

Thank you! Works great now!