Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
Which columns are input and which are calculated?
-- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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 "Sean.W" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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" wrote: Which columns are input and which are calculated? -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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 -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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" wrote: Which columns are input and which are calculated? -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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" wrote: Which columns are input and which are calculated? -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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! "Sean.W" wrote: 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? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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" wrote: Which columns are input and which are calculated? -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
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. -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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! "Sean.W" wrote: 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? "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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" wrote: Which columns are input and which are calculated? -- Gary''s Student - gsnu200857 "Sean.W" wrote: 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? |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inputting data into more than one sheet at a time
GOT IT!
Thank you. "Shane Devenshire" wrote: 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 "Sean.W" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting a length of time? | New Users to Excel | |||
Inputting data into two worksheets | Excel Discussion (Misc queries) | |||
inputting data | Excel Discussion (Misc queries) | |||
Update a chart immediately after inputting data into data source | Charts and Charting in Excel | |||
Inputting Source Data - help please | Charts and Charting in Excel |