Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
I've asked this question elsewhere with no responses. I have several worksheets, each containing data pertaining to a subset of a population. I also have a master sheet which contains all of the data from all of the subset sheets. Thus, this master sheet contains data pertaining to the entire population. THe master sheet is sorted. Currently whenever I add new data to one of the subset sheets, I manually copy this information onto the master sheet and then resort the master sheet. This is very tedious. How can I cause the master sheet to automatically update and resort every time I add new data to one of the subset sheets? Any help would be greatly appreciated. Thanks. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=551134 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
Sub CopytheRowoftheActiveCell()
With Worksheets("Master") selection.EntireRow.copy Destination:= .Cells(rows.count,1).End(xlup)(2) ..Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), _ Header:=xlYes End With End With Click in your cell or Cells/rows with the new data and run the macro. -- Regards, Tom Ogilvy "Spreadsheet" wrote: I've asked this question elsewhere with no responses. I have several worksheets, each containing data pertaining to a subset of a population. I also have a master sheet which contains all of the data from all of the subset sheets. Thus, this master sheet contains data pertaining to the entire population. THe master sheet is sorted. Currently whenever I add new data to one of the subset sheets, I manually copy this information onto the master sheet and then resort the master sheet. This is very tedious. How can I cause the master sheet to automatically update and resort every time I add new data to one of the subset sheets? Any help would be greatly appreciated. Thanks. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=551134 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
Hi Tom, THanks for the reply. I'm new to macros so if you could give me a bit of help with how to create one with this code it would be appreciated. Which parts of the code need to be customized to my specific worksheets? -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=551134 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
If the mastersheet is named something other than Master, change that.
If you want to sort on a different column than column 2, then change Key1:=.Range("B2") to Key1:=.range("F2") (for example to sort on column F) If your data on the master sheet doesn't start with row header in row A starting in column 1 then that would need to be altered. Sub CopytheRowoftheActiveCell() With Worksheets("Master") selection.EntireRow.copy Destination:= .Cells(rows.count,1).End(xlup)(2) ..Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), _ Header:=xlYes End With End With All testing should be performed on a copy of your workbook incase it all turns awry. -- Regards, Tom Ogilvy "Spreadsheet" wrote: Hi Tom, THanks for the reply. I'm new to macros so if you could give me a bit of help with how to create one with this code it would be appreciated. Which parts of the code need to be customized to my specific worksheets? -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=551134 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
VB didn't like parts of the code, so I changed it to this: Sub CopytheRowoftheActiveCell() With Worksheets("All") Selection.EntireRow.Copy Destination:=.Cells(Rows.Count 1).End(xlUp)(2) Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), _ Header:=xlYes End With End Sub When I ran the macro, it said that the sort reference was not valid -- Spreadshee ----------------------------------------------------------------------- Spreadsheet's Profile: http://www.excelforum.com/member.php...fo&userid=3473 View this thread: http://www.excelforum.com/showthread.php?threadid=55113 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Gathering Data
You almost got it right:
Sub CopytheRowoftheActiveCell() With Worksheets("All") Selection.EntireRow.Copy Destination:= _ .Cells(Rows.Count, 1).End(xlUp)(2) .Range("A1").CurrentRegion.Sort _ Key1:=.Range("B2"), Header:=xlYes End With End Sub This assumes row 1 on "All" has your headers in it. -- Regards, Tom Ogilvy "Spreadsheet" wrote in message ... VB didn't like parts of the code, so I changed it to this: Sub CopytheRowoftheActiveCell() With Worksheets("All") Selection.EntireRow.Copy Destination:=.Cells(Rows.Count, 1).End(xlUp)(2) Range("A1").CurrentRegion.Sort Key1:=.Range("B2"), _ Header:=xlYes End With End Sub When I ran the macro, it said that the sort reference was not valid. -- Spreadsheet ------------------------------------------------------------------------ Spreadsheet's Profile: http://www.excelforum.com/member.php...o&userid=34730 View this thread: http://www.excelforum.com/showthread...hreadid=551134 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Gathering Data using excel | Excel Discussion (Misc queries) | |||
Gathering data using a form | Excel Discussion (Misc queries) | |||
gathering data | Excel Discussion (Misc queries) | |||
gathering data | Excel Programming | |||
Gathering data | Excel Programming |