| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
enter data on 1 sheet and make it enter on next avail row on 2nd s
I would like to enter data into lists on 3 different sheets and I would like
that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#2
|
|||
|
|||
|
Hi
this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#3
|
|||
|
|||
|
Hello Frank,
I don't know about you Nadia, but I sure would like to know! I still think these newsgroups are the best learning tool!!!., and the best thing since sliced bread!! Thank you in advance!!!!! and Happy Holidays to all! André "Frank Kabel" wrote in message ... Hi this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#4
|
|||
|
|||
|
Andre
Stick this code in your 3 worksheets. Private Sub Worksheet_Change(ByVal Target As Range) ''when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("A" & n).Copy Destination:= _ Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Wed, 15 Dec 2004 18:58:50 GMT, "Andre Croteau" wrote: Hello Frank, I don't know about you Nadia, but I sure would like to know! I still think these newsgroups are the best learning tool!!!., and the best thing since sliced bread!! Thank you in advance!!!!! and Happy Holidays to all! André "Frank Kabel" wrote in message ... Hi this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#5
|
|||
|
|||
|
Thank you Gord, this is brilliant!!!
I will definitely be asking lots more questions here. cheers, Nadia "Gord Dibben" wrote: Andre Stick this code in your 3 worksheets. Private Sub Worksheet_Change(ByVal Target As Range) ''when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("A" & n).Copy Destination:= _ Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Wed, 15 Dec 2004 18:58:50 GMT, "Andre Croteau" wrote: Hello Frank, I don't know about you Nadia, but I sure would like to know! I still think these newsgroups are the best learning tool!!!., and the best thing since sliced bread!! Thank you in advance!!!!! and Happy Holidays to all! André "Frank Kabel" wrote in message ... Hi this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#6
|
|||
|
|||
|
Hi Gord,
The code works great..... NOW... how can I amend this to get data from more than 1 cell on the same row to do the same, e.g. I want to type data in colums A:I. many thanx "Gord Dibben" wrote: Andre Stick this code in your 3 worksheets. Private Sub Worksheet_Change(ByVal Target As Range) ''when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("A" & n).Copy Destination:= _ Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Wed, 15 Dec 2004 18:58:50 GMT, "Andre Croteau" wrote: Hello Frank, I don't know about you Nadia, but I sure would like to know! I still think these newsgroups are the best learning tool!!!., and the best thing since sliced bread!! Thank you in advance!!!!! and Happy Holidays to all! André "Frank Kabel" wrote in message ... Hi this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#7
|
|||
|
|||
|
Me AGAIN...
Ive copied and made changes to the IF statement so that data from A:I in "sheet 1" also appears in the "Summary" sheet A:I... however, if one of the cells in "sheet 1" is blank the rest of the data jumps up 1 row in the "summary" sheet.. how can I fix this. cheers, Nadia "Nadia" wrote: Hi Gord, The code works great..... NOW... how can I amend this to get data from more than 1 cell on the same row to do the same, e.g. I want to type data in colums A:I. many thanx "Gord Dibben" wrote: Andre Stick this code in your 3 worksheets. Private Sub Worksheet_Change(ByVal Target As Range) ''when entering data in a cell in Col A On Error GoTo enditall Application.EnableEvents = False If Target.Cells.Column = 1 Then n = Target.Row If Excel.Range("A" & n).Value < "" Then Excel.Range("A" & n).Copy Destination:= _ Sheets("Summary").Cells(Rows.Count, 1).End(xlUp) _ .Offset(1, 0) End If End If enditall: Application.EnableEvents = True End Sub Gord Dibben Excel MVP On Wed, 15 Dec 2004 18:58:50 GMT, "Andre Croteau" wrote: Hello Frank, I don't know about you Nadia, but I sure would like to know! I still think these newsgroups are the best learning tool!!!., and the best thing since sliced bread!! Thank you in advance!!!!! and Happy Holidays to all! André "Frank Kabel" wrote in message ... Hi this would be only possible using vBA event procedures (e.g. worksheet change event handlers). Do you want to go this way? -- Regards Frank Kabel Frankfurt, Germany Nadia wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#8
|
|||
|
|||
|
Can you send me the final code you used to do this. I cannot seem to get mine
to work. I am trying to type something in one sheet and automatically have it available in a summary sheet in excel. thanks! "Nadia" wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
|
#9
|
|||
|
|||
|
Can you send me the actual final code you used to do this? I cant seem to get
it to work. thanks! "Nadia" wrote: I would like to enter data into lists on 3 different sheets and I would like that data to automatically collate on a summary sheet (i.e. as soon as I hit enter after typing the data in one of the 3 sheets it should appear on the summary sheet on the next available row). Can this be done?? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatioc spill over data to a new Excel sheet from Xml source? | Excel Discussion (Misc queries) | |||
| Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
Hybrid Mode
