Although I have been using Excel for many years, i have never had to write a VBA code and in the present workbook I am working on, the time has come and I am totally at sea!
I am using Excel 2007 and basically what I want to do is to have two worksheets in the workbook - one called Workout and the other called Graph (which are all set up already).
I want to have figures put into cells in Workout (say the input cells are I8, I13 and I18) then those figures to transfer on to Graph in columns B, C and D respectively - that's the easy part! But now I want the person inputting figures to be able to change the figures in Workout next week and for those figures to transfer to the Graph worksheet but the row under last week's input. So, one week the figures will transfer to B1, C1, D1 etc and the next they will transfer to B2, C2, D2, etc leaving the last week's figures in Graph intact.
I have tried using the following formula but nothing seems to happen when I save it and try to input figures. I am really, really new to this VBA stuff and would appreciate someone coming back to me and explaining as simply as possible what I am doing wrong or what I should be doing. Here is the code I am using now to try and at least get one cell to transfer:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$8" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Target.Copy Destination:=Worksheets("Graph").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True
End Sub
Many thanks, in adance
Nikwak
I am using Excel 2007 and basically what I want to do is to have two worksheets in the workbook - one called Workout and the other called Graph (which are all set up already).
I want to have figures put into cells in Workout (say the input cells are I8, I13 and I18) then those figures to transfer on to Graph in columns B, C and D respectively - that's the easy part! But now I want the person inputting figures to be able to change the figures in Workout next week and for those figures to transfer to the Graph worksheet but the row under last week's input. So, one week the figures will transfer to B1, C1, D1 etc and the next they will transfer to B2, C2, D2, etc leaving the last week's figures in Graph intact.
I have tried using the following formula but nothing seems to happen when I save it and try to input figures. I am really, really new to this VBA stuff and would appreciate someone coming back to me and explaining as simply as possible what I am doing wrong or what I should be doing. Here is the code I am using now to try and at least get one cell to transfer:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$I$8" Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Target.Copy Destination:=Worksheets("Graph").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
Application.ScreenUpdating = True
End Sub
Many thanks, in adance
Nikwak