Help with a running total column

[kcompton]

Hello-
Is there a way that when I change the date in Cell A1, the numbers in column A are updated with the numbers in column C? ANd the numbers in Column B revert to zero?

Thank you for your help.


Excel 2010

	A	B	C
1	3/26/2013
2	Starting Enrollment	New This week	Total Enrollment
3	5	5	10
4	10	5	15
5	15	6	21
6	20	4	24
7	25	4	29

<TBODY>
</TBODY>

Sheet1​

 

[Teeroy]

Hi there,

Place the following macro in the "Sheet" code module of the worksheet the data is on. It must go in the correct code module (not a standard module) as it is triggered by an event rather than called.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
    Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy
    Range("A3").PasteSpecial xlPasteValues
    Range("B3:B" & Range("B" & Rows.Count).End(xlUp).Row).Value = 0
    Application.CutCopyMode = False
End If
End Sub

 

[kcompton]

Thank you! Your solution worked perfectly.

 

[Teeroy]

You are welcome, and thanks for the feedback.

 

[kcompton]

Teroy - Thank you for the solution again. I now need to lock selected cells and protect the worksheet.

I unlocked the cells the user may enter data into and locked cells they may not change. I saved it as a macro enabled worksheet. Then I protected the worksheet with the "select ulocked cells" toggled on in the "allow all users of this worksheet to:" section. The Proitect worksheet and contects of locked cells is toggled on. After saving the worksheet again, and changing the date (the event that triggers the macro), I received a "Run time error 1004: PasteSpecila methd of Range class failed" error.

The Debug highlighted Range("A3").PasteSpecial xlPasteValues.

Here is the Code I used for the worksheet included in this post:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("D3:D" & Range("C" & Rows.Count).End(xlUp).Row).Copy
Range("A3").PasteSpecial xlPasteValues
Range("B3:B" & Range("B" & Rows.Count).End(xlUp).Row).Value = 0
Range("C3:C" & Range("C" & Rows.Count).End(xlUp).Row).Value = 0
Application.CutCopyMode = False
End If
End Sub

The user may change A1, B3-B7, C3-C7. The rest of the cells are locked. When the user changes the date in A1, it triggers the macro.

Sheet1

*	A	B	C	D	E	F	G
1	3/32/2013	*	*	*	*	*	*
2	Last Week's Enrollment	New This week	Withdrawn	Total Enrollment	*	*	Last Week's Enrollment
3	49	5	1	53	*	*	49
4	61	5	1	65	*	*	61
5	67	5	1	71	*	*	67
6	75	5	1	79	*	*	75
7	72	5	1	76	*	*	72
8	*	*	*	*	*	*	*
9	save as macro-enabled worksheet

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell Formula D3 =SUM(A3+B3)-C3 G3 =A3 D4 =SUM(A4+B4)-C4 G4 =A4 D5 =SUM(A5+B5)-C5 G5 =A5 D6 =SUM(A6+B6)-C6 G6 =A6 D7 =SUM(A7+B7)-C7 G7 =A7 <TBODY> </TBODY>

<TBODY>
</TBODY>

Thank you for looking at this.

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

 

[Teeroy]

When the sheet is protected manually changes to locked cells are not permitted, even by VBA. You need to protect the sheet by code with

sheets("Sheet name").Protect Password:="abc", UserInterfaceOnly:=True

which allows changes to be made by code, but not the user.

 

[kcompton]

I'm sorry - I don't understand "which allows changes to be made by code, but not the user" -
when I protect the worksheet as a macro-enabled worksheet, changes are not allowed in the Unlocked cells.
Thanks again for your help
Also- where would I place that line of code?

 

[Teeroy]

With UserInterfaceOnly:=True protection applies to the worksheet as normal for the user (they can only change unlocked cells) but the macro can make changes to any locked or unlocked cells. You don't need to use it in your sub, you can apply it by its own sub (see below), or even as a single line in the immediate window of the VBE.

Sub set_protection()
With Sheets("Sheet1")
    .Protect Password:="abc", UserInterfaceOnly:=True
End With
End Sub

 

[kcompton]

Thank you - I have not been able to make this solution work for the protected worksheet. I pasted the code:
sheets("Sheet name").Protect Password:="abc", UserInterfaceOnly:=True
into the Immediate window and saved the workbook as an macro enabled workbook file -
The event worked but the locked cells were able to be edited.

When I added the line if code to the Sheet Code Module and saved the workbook as a macro-enabled workbook, I revceived a Runptime error 9: Subscript out of range.

When I added the code
Sub set_protection()
With Sheets("Sheet1")
.Protect Password:="abc", UserInterfaceOnly:=True
End With
End Sub

as its own sub, the event worked but the locked cells were able to be edited.

I did not manually protect the sheet in either example
What am I doing wrong?
Again, many thanks for your help

 

[Teeroy]

This seems very strange. If you can you post your test file on a file sharing site (such as Box) and post a link to it I'll have a look at the file.