copying from one worksheet to another
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. |
copying from one worksheet to another
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:D in same row. Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Columns("B:D"), 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" skrev i meddelelsen ... 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. |
copying from one worksheet to another
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:D in same row. Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Columns("B:D"), 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" skrev i meddelelsen ... 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. |
copying from one worksheet to another
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:D"), 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" skrev i meddelelsen ... 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:D in same row. Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Columns("B:D"), 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" skrev i meddelelsen ... 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. |
copying from one worksheet to another
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:D"), 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" skrev i meddelelsen ... 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:D in same row. Private Sub Worksheet_Change(ByVal Target As Range) Set isect = Intersect(Columns("B:D"), 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" skrev i meddelelsen ... 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. |
All times are GMT +1. The time now is 07:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com