| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Worksheet formula incorrect after copying to another worksheet | Excel Worksheet Functions | |||
| Copying worksheet cells into another worksheet using autofill | Excel Discussion (Misc queries) | |||
| Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
| Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions | |||
| Copying Worksheet triggers Click event of combobox on another worksheet | Excel Programming | |||
Linear Mode
