Macro Help

[pujo]

Hey Gang,
I am trying to figure this out. I haven't had any luck, maybe someone could shed a little light on the subject.
Here is the delima.....

I have 8 counters in cell A1:A8, A9 is the sum of A1:A8. These are daily totals.

In cell I1:I31 is the days of the month, with cell I32 the sum of I1:I31

How could I use a macro to move the value of A9 to the the column that I use for the day of the month.

An on change macro would work to move the vlaue to the next empty cell on colomn "I" but it doesnt work correctly. everytime I change one of the 8 counters, it gets copied to the next empty cell in column "I". I would like for it to only get copied once all 8 cells are updated.
I hope someone can understand what I am asking, as I really don't know how to explain any other way.
Thanks,
Pujo

 

[VoG]

If you fill A1:A8 in sequence (i.e. start at A1 and work down) then try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A8" Then
    Application.EnableEvents = False
    Target.Offset(1).Copy Destination:=Range("I" & Rows.Count).End(xlUp).Offset(1)
    Application.EnableEvents = True
End If
End Sub

 

[pujo]

Thanks Peter,
That seems to work but with an error.
After all cells are updates, it does move to column "I", but not the value. In column "I" I get "#REF!" or in the formula bar it reads, =SUM(REF!).
Did I miss something?
Thanks for the help.
Pujo

 

[VoG]

In that case try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A8" Then
    Application.EnableEvents = False
    Target.Offset(1).Copy
    Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True
End If
End Sub

 

[pujo]

That works perfect!
Thanks Peter.

How do I get rid of the blinking ring of cell A9 after it is copied to column "I"? Can I do this also in the macro? It does simply go away if I hit the Esc key.

Thanks,
Pujo

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A8" Then
    Application.EnableEvents = False
    Target.Offset(1).Copy
    Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True
    Application.CutCopyMode = False
End If
End Sub

 

[pujo]

Again, a many thanks!
Have a great day!
Pujo

 

[pujo]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A8" Then
    Application.EnableEvents = False
    Target.Offset(1).Copy
    Range("I" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Application.EnableEvents = True
    Application.CutCopyMode = False
End If
End Sub

Peter,
The line in red, can this be a range say I10:I20 rather than the column itself? It does work great, but thought of another application I can use this for.
Thanks,
Pujo

 

[VoG]

Sorry, I don't understand. What is supposed to happen?

 

[pujo]

Instead oof just pasting to the first empty cell in column "I", I was thinking if I could get it to paste to the first empty cell in a range. So starting at cell I15 find the first empty cell, but dont go past cell I20,
(just an example)
Something like I did in the code below, it wont work like that but maybe you can get a better understating of what I am trying to say.

Private Sub Worksheet_Change(ByVal Target As Range) If  Target.Address(False, False) = "A8" Then     Application.EnableEvents =  False     Target.Offset(1).Copy     [COLOR=red][COLOR=Black]Range[/COLOR]("[U]I15:I20[/U]" [COLOR=Black]& Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues[/COLOR][/COLOR]     Application.EnableEvents = True     Application.CutCopyMode = False End If End Sub