enter data on 1 sheet and make it enter on next avail row on 2nd s

N

Nadia

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

Frank Kabel

Hi
this would be only possible using vBA event procedures (e.g. worksheet
change event handlers). Do you want to go this way?
 
A

Andre Croteau

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é
 
G

Gord Dibben

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
 
N

Nadia

Thank you Gord, this is brilliant!!!
I will definitely be asking lots more questions here.
cheers,
Nadia
 
N

Nadia

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
 
N

Nadia

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
 
N

Nadia

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
 
G

Gord Dibben

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
 
D

Dave Peterson

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
 
A

Andre Croteau

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é
 
D

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).
 
D

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 here:

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.
 
N

Nadia

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.
 
D

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?
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.
Click to expand...
 
N

Nadia

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 said:
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?
Click to expand...
 
N

Nadia

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 said:
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?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell data entering slow! 0
How can I print rows that go outside of the page without shrinking the text? 0
How do I get data into next available cell in column? 1
Macro help?: move row to other sheet when text is entered in cell 0
Summarise data on multiple sheets 1
Make elements in a row space between instead of wrapping when choosing multiple columns 0
Summarize the monthly recrods by name 3
Suppressing Blank Rows 0

Top