Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 15th 04, 06:19 AM
Nadia
 
Posts: n/a
Default 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   Report Post  
Old December 15th 04, 07:46 AM
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Old December 15th 04, 06:58 PM
Andre Croteau
 
Posts: n/a
Default

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   Report Post  
Old December 15th 04, 09:50 PM
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Old December 15th 04, 10:05 PM
Nadia
 
Posts: n/a
Default

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   Report Post  
Old December 15th 04, 10:15 PM
Nadia
 
Posts: n/a
Default

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   Report Post  
Old December 15th 04, 11:17 PM
Nadia
 
Posts: n/a
Default

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   Report Post  
Old December 16th 04, 12:37 AM
Gord Dibben
 
Posts: n/a
Default

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??






  #9   Report Post  
Old December 16th 04, 01:09 AM
Nadia
 
Posts: n/a
Default

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??







  #10   Report Post  
Old December 16th 04, 01:21 AM
Gord Dibben
 
Posts: n/a
Default

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??










Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatioc spill over data to a new Excel sheet from Xml source? Sruthi Excel Discussion (Misc queries) 0 December 6th 04 06:31 PM
Extending a Chart Data Series from an Array - Can it be done? Jon Peltier Charts and Charting in Excel 4 November 30th 04 03:30 AM


All times are GMT +1. The time now is 07:37 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017