Inputting data into more than one sheet at a time

[Sean.W]

I have four worksheets containing sales figures for four different products.
I then have a sheet called "MASTER" which I would like to contain data from
all four of the other sheets. What I would like to do, is whenever I input
data into any one of the four product sheets, it should also appear on the
"MASTER" sheet, thus creating a compilation of the data on all four sheets.

All 5 worksheets have the same columns:
date/item/$cost/$retail/$reimb./$difference/client name

How can I accomplish this?

 

[Shane Devenshire]

Hi,

You probably will need to use VBA (macro programming). If that is what you
want it would be good to show us examples of a product sheet and the master
sheet.

 

[Sean.W]

A____B____C____D_____E______F_________G_____
date/item/$cost/$retail/$reimb./$difference/client name

A = INPUT
B = INPUT
C = INPUT
D = INPUT
E = INPUT
F = E MINUS C
G = INPUT

 

[Gary''s Student]

Shane is correct.

Here is a typical event macro that would be inserted in the worksheet code
area of each sheet, except the MASTER sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msh As Worksheet, rXfr As Range, rw As Long, recept As Long
Set Msh = Sheets("MASTER")
rw = Target.Row
Set rXfr = Range(Cells(rw, 1), Cells(rw, 7))
If Application.WorksheetFunction.CountBlank(rXfr) <> 0 Then Exit Sub
If Msh.Cells(1, 1).Value = "" Then
recept = 1
Else
recept = Msh.Cells(Rows.Count, "A").End(xlUp).Row + 1
End If
rXfr.Copy Msh.Cells(recept, 1)
End Sub

The macro waits until there are no blanks in A thru G of the row being
editted.
It then copies the completed material to the next available row in the
MASTER sheet.


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Sean.W]

Thank you!
I've put in the code and saved it. Should "MASTER" in your sample code be
the name of the target worksheet? (which is called MASTER, but not sure if it
needs to say "Worksheet 5" instead)

It doesn't seem to be pulling the data across...

At what point should it pull the data across to my master sheet? After
inputting and pressing Enter?

 

[Sean.W]

UPDATE: It seems to be pulling data from somewhere else... on my MASTER sheet
I now have dozens of duplicates of various rows from the other four sheets.

I noticed in your code the "range" referring to "rw1 - rw7" i'm assuming
(since I know nothing about VB) that this means rows 1 through 7. Correct me
if I'm wrong, but should the range refer to the COLUMNS, not the ROWS? If so,
how do I correct the code?

Thank you for your continued help!

 

[Gary''s Student]

I can't reproduce your problem.

In my code rw will be the row into which you are entering data.

Set rXfr = Range(Cells(rw, 1), Cells(rw, 7)) specifies cols A thru G in that
row.

REMEMBER:

The code CANNOT be put in the MASTER tab, only the data tabs! If the code
is in the MASTER tab, you will see lots of duplicate entries.

 

[Sean.W]

GOT IT!

Thank you.

Hi,

You probably will need to use VBA (macro programming). If that is what you
want it would be good to show us examples of a product sheet and the master
sheet.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire