On User Input into Cell, copy that value to other cells

[blazer003]

I'm a relative novice when in comes to excel. I know my way around the basic formulas and even delve into if then formulas in excel, but I have no experience with macros. I thought this would be a relatively easy problem, but I have searched for an hour and not found an answer.

Here's the simple question: When a use inputs a value into A2, upon hitting enter, I want that value to be copied to G2. However, it cannot be a simple reference in G2 to A2, as Column A gets wiped once a month, but I want the values in G2 to remain (unless a new value is input into A2, in which case it would then overwrite the value in G2).

Does that make sense? Here's a bit more detail on why I need this:

I have a cell, we'll say A2, where a user can enter the total number of members in their family for our food pantry.

Over in cell G2, we have a record of this number.

At the end of the month, we clear column A (guests to our food pantry come once a month), but leave G (with the value of members in their family remaining for later reference.)

If our users input a new value next month in A2 (members in family have changed, or a mistake was made), we want it to replace what has been saved from a previous month in G2. If it is a brand new family, then G2 is blank and it puts the value input from A2 to G2. However, we want the value to remain in G2 even when A2 is cleared.

So I basically need a formula that says: Once a value has been input to Column A, Copy and paste that into Column G.

Thank you so much for anyone that can give me a pointer on how to make this happen, and apologies in advance if this is a question that is well answered, I really did take time to try to find an answer before coming here.

 

[JLGWhiz]

You do that with event code. Copy this to the worksheet code module in the sheet where you want the copy action to take place.
To access the sheet code module, right click the sheet name tab then click 'View Code' in the pop up menu. After you paste the code into the code window, close the VB editor and save the workbook as a macro enabled workbook to preserve the code. The code will run when changes are made to the worksheet but will only execute when a change occurs in cell A2.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("G2") = Range("A2").Value
    End If
Application.EnableEvents = True
End Sub

You can't get the value into G2 as a stand alone without VBA or orther code.