|
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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
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?? |
Nadia
Post the amended code for us to look at. Gord On Wed, 15 Dec 2004 15:17:05 -0800, "Nadia" wrote: 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?? |
Its actually NOT working so pointless posting. All I did was copy the
original for "A" and pasted and changed it for "B" etc. and changed column offset #. It only works if the data is entered into "B:I" first and "A" last. If I type data in "A" first it all stops. I understand why this is happening as it is following the rules of IF and obviously data in "A" satisfies the first IF and it stops. So obviously the amended code is all wrong. So Im back to the beginning where I need data from A:I duplicated on the next available row in "Summary" but if there is a blank cell, move one cell to the right NOT up. Frustrated! Nadia "Gord Dibben" wrote: Nadia Post the amended code for us to look at. Gord On Wed, 15 Dec 2004 15:17:05 -0800, "Nadia" wrote: 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?? |
Nadia
My VBA skills are not advanced enough to assist very much. Hopefully someone will jump in and amend my original code to do what you want. TIA to "someone" Gord On Wed, 15 Dec 2004 17:09:01 -0800, "Nadia" wrote: Its actually NOT working so pointless posting. All I did was copy the original for "A" and pasted and changed it for "B" etc. and changed column offset #. It only works if the data is entered into "B:I" first and "A" last. If I type data in "A" first it all stops. I understand why this is happening as it is following the rules of IF and obviously data in "A" satisfies the first IF and it stops. So obviously the amended code is all wrong. So Im back to the beginning where I need data from A:I duplicated on the next available row in "Summary" but if there is a blank cell, move one cell to the right NOT up. Frustrated! Nadia "Gord Dibben" wrote: Nadia Post the amended code for us to look at. Gord On Wed, 15 Dec 2004 15:17:05 -0800, "Nadia" wrote: 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?? |
PMFJI,
the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson |
Hi Dave,
You are a genius!!!! Thank you very very much. Nadia :) "Dave Peterson" wrote: PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson |
Hello Dave and Gord....this is great stuff!!
Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson |
Dave
Also great thanks from me, you "someone", you<g Gord On Wed, 15 Dec 2004 19:41:01 -0800, "Nadia" wrote: Hi Dave, You are a genius!!!! Thank you very very much. Nadia :) "Dave Peterson" wrote: PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson |
I'm still not sure I'd use something like this.
I think I'd just copy the rows I wanted to copy with just one macro at the end. Making a typo is still scary to me (but I've said that before). Gord Dibben wrote: Dave Also great thanks from me, you "someone", you<g Gord On Wed, 15 Dec 2004 19:41:01 -0800, "Nadia" wrote: Hi Dave, You are a genius!!!! Thank you very very much. Nadia :) "Dave Peterson" wrote: PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson |
I still would do all my typing (just for the sake of a quick validation check),
then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson |
Hi guys,
is there a quick way to have the existing data update on the Summary sheet when changes are made to the other 3 sheets?? At the moment it will recopy as a new record. "Dave Peterson" wrote: I still would do all my typing (just for the sake of a quick validation check), then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson |
This kind of thing becomes a big old problem (in my mind, anyway).
I like to keep my "real" data in one spot and split it when I have to. Each of the "splits" is a report only worksheet/workbook. If changes have to be made, then you have to go back to the real source and make the changes and regenerate the reports. But you could have some equivalent code in each sheet that copy/move data over, but man, oh, man, it can get really messy really fast. (You make a change with an error in it. You have to find the place that received the error and clean it up. And then reenter to fix the spot that should have gotten it.) You add a record, but there's a record that corresponds to that record in the other sheet. It shouldn't be added to--it should be replaced/updated. I surely wouldn't do this--not as much for code problems--just standard human problems. Did I convince you not to do it? Nadia wrote: Hi guys, is there a quick way to have the existing data update on the Summary sheet when changes are made to the other 3 sheets?? At the moment it will recopy as a new record. "Dave Peterson" wrote: I still would do all my typing (just for the sake of a quick validation check), then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
I had a feeling that it would be rather complex. "you dont ask...you dont get!"
It was worth a shot, but I can live with it. Thank you for all your time, effort and consideration Nadia :-) "Dave Peterson" wrote: This kind of thing becomes a big old problem (in my mind, anyway). I like to keep my "real" data in one spot and split it when I have to. Each of the "splits" is a report only worksheet/workbook. If changes have to be made, then you have to go back to the real source and make the changes and regenerate the reports. But you could have some equivalent code in each sheet that copy/move data over, but man, oh, man, it can get really messy really fast. (You make a change with an error in it. You have to find the place that received the error and clean it up. And then reenter to fix the spot that should have gotten it.) You add a record, but there's a record that corresponds to that record in the other sheet. It shouldn't be added to--it should be replaced/updated. I surely wouldn't do this--not as much for code problems--just standard human problems. Did I convince you not to do it? Nadia wrote: Hi guys, is there a quick way to have the existing data update on the Summary sheet when changes are made to the other 3 sheets?? At the moment it will recopy as a new record. "Dave Peterson" wrote: I still would do all my typing (just for the sake of a quick validation check), then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
I had a feeling this would be complex. "if you dont ask... you dont get".
It was worth a shot, but I can live with it. Thank you very much for all your time, effort and consideration. Nadia :-) "Dave Peterson" wrote: This kind of thing becomes a big old problem (in my mind, anyway). I like to keep my "real" data in one spot and split it when I have to. Each of the "splits" is a report only worksheet/workbook. If changes have to be made, then you have to go back to the real source and make the changes and regenerate the reports. But you could have some equivalent code in each sheet that copy/move data over, but man, oh, man, it can get really messy really fast. (You make a change with an error in it. You have to find the place that received the error and clean it up. And then reenter to fix the spot that should have gotten it.) You add a record, but there's a record that corresponds to that record in the other sheet. It shouldn't be added to--it should be replaced/updated. I surely wouldn't do this--not as much for code problems--just standard human problems. Did I convince you not to do it? Nadia wrote: Hi guys, is there a quick way to have the existing data update on the Summary sheet when changes are made to the other 3 sheets?? At the moment it will recopy as a new record. "Dave Peterson" wrote: I still would do all my typing (just for the sake of a quick validation check), then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
The complexity of the code (just to add to the bottom) is pretty much the same
as what you've seen. It'll get a little more complex if you want to update/delete/add rows based on what you do on another sheet. The complexity isn't really in the code that does the work. The complexity (I think) is in the code that makes sure what's done is correct. (Did that make sense??) I find it really difficult to guess how I can screw up and make sure I can recover. I find it almost impossible to guess how others will screw up! Nadia wrote: I had a feeling this would be complex. "if you dont ask... you dont get". It was worth a shot, but I can live with it. Thank you very much for all your time, effort and consideration. Nadia :-) "Dave Peterson" wrote: This kind of thing becomes a big old problem (in my mind, anyway). I like to keep my "real" data in one spot and split it when I have to. Each of the "splits" is a report only worksheet/workbook. If changes have to be made, then you have to go back to the real source and make the changes and regenerate the reports. But you could have some equivalent code in each sheet that copy/move data over, but man, oh, man, it can get really messy really fast. (You make a change with an error in it. You have to find the place that received the error and clean it up. And then reenter to fix the spot that should have gotten it.) You add a record, but there's a record that corresponds to that record in the other sheet. It shouldn't be added to--it should be replaced/updated. I surely wouldn't do this--not as much for code problems--just standard human problems. Did I convince you not to do it? Nadia wrote: Hi guys, is there a quick way to have the existing data update on the Summary sheet when changes are made to the other 3 sheets?? At the moment it will recopy as a new record. "Dave Peterson" wrote: I still would do all my typing (just for the sake of a quick validation check), then copy the rows to the other sheets all at once. Maybe you can steal some code from Debra Dalgleish's site: There are a couple of files he http://www.contextures.com/excelfiles.html Create New Sheets from Filtered List -- uses an Advanced Filter to create separate sheet of orders for each sales rep visible in a filtered list; macro automates the filter. AdvFilterRepFiltered.xls 35 kb Update Sheets from Master -- uses an Advanced Filter to send data from Master sheet to individual worksheets -- replaces old data with current. AdvFilterCity.xls 55 kb But if you want... Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range Dim testWks As Worksheet 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If Set testWks = Nothing On Error Resume Next Set testWks = Me.Parent.Worksheets(Target.Value) On Error GoTo 0 If testWks Is Nothing Then 'doesn't match an existing worksheet 'it could mean that the worksheet is missing, too, 'but I'm guessing that it'll probably be a typo MsgBox "Please fix the value in: " & Target.Address(0, 0) Exit Sub End If With testWks Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub This actually allows any entry in column J and looks for a worksheet to match. If that's a problem, the macro could be changed to only look for those 3 entries. Andre Croteau wrote: Hello Dave and Gord....this is great stuff!! Might I ask a variation of this great application? Suppose I have data in a master sheet, with data in cells A:I, with column J with the name of 3 possible sheet names QLD, NSW, WA in the same workbook I would like the data from the master sheet to go to their respective individual sheets depending on the value of the cell entered in column J. Thank you in advance. André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Hi Again Dave,
I believe this must be close to the record number of threads.... I have a final(?) question regarding the original code: The original code works well, but I would like to copy/PasteSpecialValues from any of the 3 sheets to the "Summary" sheet Your code has a straight "Copy / Paste" procedures, so that formulas are also copied, which is not always the best thing to do. Is there one last line code you can change/modify? Thanks in advance, and also for the answer to my other question André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson |
If all you're interested in is the value, you could just assign the values to
the other cells. I just kept the bottom portion that needed changing: On Error GoTo errHandler: Application.EnableEvents = False destCell.Resize(1, 9).Value _ = Target.EntireRow.Resize(1, 9).Value Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub or if you want to paste values: On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy destCell.PasteSpecial Paste:=xlPasteValues Target.Value = "Copied" Application.CutCopyMode = False Beep errHandler: Application.EnableEvents = True End Sub Andre Croteau wrote: Hi Again Dave, I believe this must be close to the record number of threads.... I have a final(?) question regarding the original code: The original code works well, but I would like to copy/PasteSpecialValues from any of the 3 sheets to the "Summary" sheet Your code has a straight "Copy / Paste" procedures, so that formulas are also copied, which is not always the best thing to do. Is there one last line code you can change/modify? Thanks in advance, and also for the answer to my other question André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson |
And I don't think it's close to the longest thread.
Even with this response! <vbg Andre Croteau wrote: Hi Again Dave, I believe this must be close to the record number of threads.... I have a final(?) question regarding the original code: The original code works well, but I would like to copy/PasteSpecialValues from any of the 3 sheets to the "Summary" sheet Your code has a straight "Copy / Paste" procedures, so that formulas are also copied, which is not always the best thing to do. Is there one last line code you can change/modify? Thanks in advance, and also for the answer to my other question André <<snipped |
Hello Dave,
This is perfect! Sad to see the end of the Thread!! Happy Holidays!! André "Dave Peterson" wrote in message ... If all you're interested in is the value, you could just assign the values to the other cells. I just kept the bottom portion that needed changing: On Error GoTo errHandler: Application.EnableEvents = False destCell.Resize(1, 9).Value _ = Target.EntireRow.Resize(1, 9).Value Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub or if you want to paste values: On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy destCell.PasteSpecial Paste:=xlPasteValues Target.Value = "Copied" Application.CutCopyMode = False Beep errHandler: Application.EnableEvents = True End Sub Andre Croteau wrote: Hi Again Dave, I believe this must be close to the record number of threads.... I have a final(?) question regarding the original code: The original code works well, but I would like to copy/PasteSpecialValues from any of the 3 sheets to the "Summary" sheet Your code has a straight "Copy / Paste" procedures, so that formulas are also copied, which is not always the best thing to do. Is there one last line code you can change/modify? Thanks in advance, and also for the answer to my other question André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson |
Glad it works for you.
(one more didn't hurt too much!) Andre Croteau wrote: Hello Dave, This is perfect! Sad to see the end of the Thread!! Happy Holidays!! André "Dave Peterson" wrote in message ... If all you're interested in is the value, you could just assign the values to the other cells. I just kept the bottom portion that needed changing: On Error GoTo errHandler: Application.EnableEvents = False destCell.Resize(1, 9).Value _ = Target.EntireRow.Resize(1, 9).Value Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub or if you want to paste values: On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy destCell.PasteSpecial Paste:=xlPasteValues Target.Value = "Copied" Application.CutCopyMode = False Beep errHandler: Application.EnableEvents = True End Sub Andre Croteau wrote: Hi Again Dave, I believe this must be close to the record number of threads.... I have a final(?) question regarding the original code: The original code works well, but I would like to copy/PasteSpecialValues from any of the 3 sheets to the "Summary" sheet Your code has a straight "Copy / Paste" procedures, so that formulas are also copied, which is not always the best thing to do. Is there one last line code you can change/modify? Thanks in advance, and also for the answer to my other question André "Dave Peterson" wrote in message ... PMFJI, the problem I have with stuff like this is that I don't know when to do the copy. You could do the copy when you finish the entry in column I (whatever finish means!). Or maybe use column J as an indicator. That would give you a chance to correct any typos in A:I without having go to the other sheet to fix it, too. I'm gonna use column J, but you could use column I if you really want. This works for me, but with my typing, I'm not sure if it would make my job easier or more difficult: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim destCell As Range 'one cell at a time only! If Target.Cells.Count 1 Then Exit Sub 'only check column J If Intersect(Target, Me.Range("J:J")) Is Nothing Then Exit Sub 'and it can't be empty! If IsEmpty(Target) Then Exit Sub 'Column A of the row must have data If IsEmpty(Me.Cells(Target.Row, "A")) Then MsgBox "Please put something in A" & Target.Row Exit Sub End If With Worksheets("summary") Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0) End With On Error GoTo errHandler: Application.EnableEvents = False Target.EntireRow.Resize(1, 9).Copy _ Destination:=destCell Target.Value = "Copied" Beep errHandler: Application.EnableEvents = True End Sub Nadia wrote: 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?? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
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?? |
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?? |
| All times are GMT +1. The time now is 07:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com