I am working with a workbook in Excel 2003

It contains two worksheets
MainIndex and PackInfo

When I enter data in the next available rows in columns of B, C and D in
MainIndex I would like it to be automatically copied into the next available
rows in columns A, B and C in PackInfo.

Is there a way to facilitate this – any advice would be appreciated to a new
programming user.

Hi

Place this in the code sheet for MainIndes. To da that, right click on the
tab for "MainIndex" sheet, click view code, paste code below.

The code will copy date, when you have entered data in columns B in same
row.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Columns("B"), Target)
TargetRow = Target.Row
If Not isect Is Nothing Then
If Not IsEmpty((Cells(TargetRow, 2))) And Not _
IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4)) Then
DestRow = Sheets("PackInfo").Range("B1").End(xlDown).Row + 1
Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
Sheets("PackInfo").Cells(DestRow, 2)
End If
End If
End Sub

Best regards,
Per

"JohnButt" <(E-Mail Removed)> skrev i meddelelsen
news:9530587C-464B-4C91-8771-(E-Mail Removed)...
>
> I am working with a workbook in Excel 2003
>
> It contains two worksheets
> MainIndex and PackInfo
>
> When I enter data in the next available rows in columns of B, C and D in
> MainIndex I would like it to be automatically copied into the next
> available
> rows in columns A, B and C in PackInfo.
>
> Is there a way to facilitate this – any advice would be appreciated to a
> new
> programming user.
>

Hi Per Jenson

Many thanks for the code - unfortunatley the data is placed in Columns B,C
and in PackInfo and not A, B and C as wanted.

I have tried altering your code but to no avail - not very conversant with
code so a little mystified.

Any further help would be apppreciated.

"Per Jessen" wrote:

> Hi
>
> Place this in the code sheet for MainIndes. To da that, right click on the
> tab for "MainIndex" sheet, click view code, paste code below.
>
> The code will copy date, when you have entered data in columns B in same
> row.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set isect = Intersect(Columns("B"), Target)
> TargetRow = Target.Row
> If Not isect Is Nothing Then
> If Not IsEmpty((Cells(TargetRow, 2))) And Not _
> IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4)) Then
> DestRow = Sheets("PackInfo").Range("B1").End(xlDown).Row + 1
> Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
> Sheets("PackInfo").Cells(DestRow, 2)
> End If
> End If
> End Sub
>
> Best regards,
> Per
>
> "JohnButt" <(E-Mail Removed)> skrev i meddelelsen
> news:9530587C-464B-4C91-8771-(E-Mail Removed)...
> >
> > I am working with a workbook in Excel 2003
> >
> > It contains two worksheets
> > MainIndex and PackInfo
> >
> > When I enter data in the next available rows in columns of B, C and D in
> > MainIndex I would like it to be automatically copied into the next
> > available
> > rows in columns A, B and C in PackInfo.
> >
> > Is there a way to facilitate this – any advice would be appreciated to a
> > new
> > programming user.
> >
>
>

Hi again

My fault, didn't read last lines carefully enough :-(

Try this code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Intersect(Columns("B"), Target)
TargetRow = Target.Row
If Not isect Is Nothing Then
If Not IsEmpty((Cells(TargetRow, 2))) And Not _
IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4)) Then
DestRow = Sheets("PackInfo").Range("A1").End(xlDown).Row + 1
Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
Sheets("PackInfo").Cells(DestRow, 1)' Cell(Row , Column)
End If
End If
End Sub

Regards,
Per

"JohnButt" <(E-Mail Removed)> skrev i meddelelsen
news:E87A0EAD-83B8-4B28-8B3D-(E-Mail Removed)...
> Hi Per Jenson
>
> Many thanks for the code - unfortunatley the data is placed in Columns B,C
> and in PackInfo and not A, B and C as wanted.
>
> I have tried altering your code but to no avail - not very conversant with
> code so a little mystified.
>
> Any further help would be apppreciated.
>
> "Per Jessen" wrote:
>
>> Hi
>>
>> Place this in the code sheet for MainIndes. To da that, right click on
>> the
>> tab for "MainIndex" sheet, click view code, paste code below.
>>
>> The code will copy date, when you have entered data in columns B in
>> same
>> row.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> Set isect = Intersect(Columns("B"), Target)
>> TargetRow = Target.Row
>> If Not isect Is Nothing Then
>> If Not IsEmpty((Cells(TargetRow, 2))) And Not _
>> IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4))
>> Then
>> DestRow = Sheets("PackInfo").Range("B1").End(xlDown).Row + 1
>> Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
>> Sheets("PackInfo").Cells(DestRow, 2)
>> End If
>> End If
>> End Sub
>>
>> Best regards,
>> Per
>>
>> "JohnButt" <(E-Mail Removed)> skrev i meddelelsen
>> news:9530587C-464B-4C91-8771-(E-Mail Removed)...
>> >
>> > I am working with a workbook in Excel 2003
>> >
>> > It contains two worksheets
>> > MainIndex and PackInfo
>> >
>> > When I enter data in the next available rows in columns of B, C and D
>> > in
>> > MainIndex I would like it to be automatically copied into the next
>> > available
>> > rows in columns A, B and C in PackInfo.
>> >
>> > Is there a way to facilitate this – any advice would be appreciated to
>> > a
>> > new
>> > programming user.
>> >
>>
>>

Many thanks - workd perfectly

Can't thank you enough

"Per Jessen" wrote:

> Hi again
>
> My fault, didn't read last lines carefully enough :-(
>
> Try this code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Set isect = Intersect(Columns("B"), Target)
> TargetRow = Target.Row
> If Not isect Is Nothing Then
> If Not IsEmpty((Cells(TargetRow, 2))) And Not _
> IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4)) Then
> DestRow = Sheets("PackInfo").Range("A1").End(xlDown).Row + 1
> Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
> Sheets("PackInfo").Cells(DestRow, 1)' Cell(Row , Column)
> End If
> End If
> End Sub
>
> Regards,
> Per
>
> "JohnButt" <(E-Mail Removed)> skrev i meddelelsen
> news:E87A0EAD-83B8-4B28-8B3D-(E-Mail Removed)...
> > Hi Per Jenson
> >
> > Many thanks for the code - unfortunatley the data is placed in Columns B,C
> > and in PackInfo and not A, B and C as wanted.
> >
> > I have tried altering your code but to no avail - not very conversant with
> > code so a little mystified.
> >
> > Any further help would be apppreciated.
> >
> > "Per Jessen" wrote:
> >
> >> Hi
> >>
> >> Place this in the code sheet for MainIndes. To da that, right click on
> >> the
> >> tab for "MainIndex" sheet, click view code, paste code below.
> >>
> >> The code will copy date, when you have entered data in columns B in
> >> same
> >> row.
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> Set isect = Intersect(Columns("B"), Target)
> >> TargetRow = Target.Row
> >> If Not isect Is Nothing Then
> >> If Not IsEmpty((Cells(TargetRow, 2))) And Not _
> >> IsEmpty(Cells(TargetRow, 3)) And Not IsEmpty(Cells(TargetRow, 4))
> >> Then
> >> DestRow = Sheets("PackInfo").Range("B1").End(xlDown).Row + 1
> >> Range(Cells(TargetRow, 2), Cells(TargetRow, 4)).Copy _
> >> Sheets("PackInfo").Cells(DestRow, 2)
> >> End If
> >> End If
> >> End Sub
> >>
> >> Best regards,
> >> Per
> >>
> >> "JohnButt" <(E-Mail Removed)> skrev i meddelelsen
> >> news:9530587C-464B-4C91-8771-(E-Mail Removed)...
> >> >
> >> > I am working with a workbook in Excel 2003
> >> >
> >> > It contains two worksheets
> >> > MainIndex and PackInfo
> >> >
> >> > When I enter data in the next available rows in columns of B, C and D
> >> > in
> >> > MainIndex I would like it to be automatically copied into the next
> >> > available
> >> > rows in columns A, B and C in PackInfo.
> >> >
> >> > Is there a way to facilitate this – any advice would be appreciated to
> >> > a
> >> > new
> >> > programming user.
> >> >
> >>
> >>
>
>