I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks in
a year, does someone know how I can repeat the code and have it change cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any help

KMF

For i = 1 to 52
If Target.Address = "AP" & i + 3 Then
Worksheets("$INV1").Range("$A$" & i + 21).Value = _
Target.Value
End If
Next i

Sharad

"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF

Untested air code:

Private Sub Worksheet_Change(ByVal Target As Range)

' (Note: Column AP = Column 42)
If Target.Column = 42 Then
Select Case Target.Row
Case 4 to 56
Worksheets("$INV1").Range("$A$" & Target.Row + 18).Value =
Target.Value
Case Else
' Do nothing if target row is less than 4 or more than 56
End Select
End If

Since you say that you have been using code similar to this, I assume
you realize that you are copying the value of the cell (Target) you are
moving TO, not the value of the cell you just LEFT.

HTH,
--
George Nicholson

Remove 'Junk' from return address.


"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF

thankyou sharad,

I am new to vis basic and I rewrote below what I am trying to accomplish. I
did not understand what you wrote and wonder if could explain further. I
rewrote below what I am trying to do.
I am using excel spreadsheets to track sales in a business. I enter sales
data each week onto a spreadsheet and was given the formula below to use so
it would
automatically be entered onto a invoice I created on a separate excel sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
I want to copy this so it works in in multiple columns that correspond to
past or future weeks. I will have to repeat this many times because of
different time periods and different customers . In other words if I enter
sales data for several products this week it would go to the invoice and I
could print it. I want to keep the data I entered foe historical reasons and
want to enter next weeks data in a new column for that week, but it would
still go to the same invoice form since after I print it the values for the
invoice form are cleared. So as i enter sales data each week in each new
column I would like it to go to the same invoice form. Can i write a code or
copy a vis basic formula that will take into account the new columns
automatically so i do not have to code every cell, just as excel works when
you copy from cell to cell it adjusts the cell address. thanks for any help.




"Sharad Naik" wrote:

For i = 1 to 52
If Target.Address = "AP" & i + 3 Then
Worksheets("$INV1").Range("$A$" & i + 21).Value = _
Target.Value
End If
Next i

Sharad

"kmf" wrote in message
...
I am using excel spreadsheets to build a account system for a business. I
want to enter sales data each week onto a spreadsheet and have it
automatically entered onto a invoice on a separate sheet. I have used this
vis basic code to do that:Private Sub Worksheet_Change(ByVal Target As
Range)
If Target.Address = "AP4" Then
Worksheets("$INV1").Range("$A$22").Value = _
Target.Value
End If
If Target.Address = "AP5" Then
Worksheets("$INV1").Range("$A$23").Value = _
Target.Value
since I have to repeat this many times because there are fifty two weeks
in
a year, does someone know how I can repeat the code and have it change
cells
automatically so i do not have to code every cell, justas excel works when
you copy from cell to cell it adjusts the cell address. thanks for any
help

KMF