copying from one worksheet to another

[JohnButt]

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.

 

[Per Jessen]

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]

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]

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]

Many thanks - workd perfectly

Can't thank you enough