Gathering Data

[Spreadsheet]

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.

[Tom Ogilvy]

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

[Spreadsheet]

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?

[Tom Ogilvy]

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

[Spreadsheet]

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.

[Tom Ogilvy]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Spreadsheet]

Thanks a bunch Tom, the macro is functional now. I just have a few concerns.

1) Right now the macro adds the selected row to the "ALL" worksheet according to the number in column B. This works very well when the new row to be added to the ALL sheet has a number in column B that is greater than any number in column B of the ALL sheet (the new row is then added to the bottom of the sheet). However, when the new row to be added to the ALL sheet has a number in column B that is less than some of the numbers in column B of the ALL sheet, the macro finds the right place for the new column and then overwrites whatever was currently in that place on the ALL sheet. This may occur because there could be many rows with the same number in column B.

SOLUTION? Can the macro insert a new row and then add the new information to it?

2) The only other issue is this: If I want to merely update a row that already exists, I don't want this row to be added to the ALL sheet, I just want the information to be updated.

SOLUTION? The macro runs a logical test to see if the number in Column C (this number is unique) of the currently selected row exists on the ALL sheet in Column C. If it does, then the row containing this number is overwritten by the currently selected row. If the number does not exist in Column C of the ALL sheet, the macro proceeds to insert a row in the correct place and writes the information there.

Are these suggestions possible? I'm not a programmer so I don't know if that would be easy or not, but it seems like it shouldn't be impossible. Thanks again for all the help.

[Tom Ogilvy]

The macro, as written, always copies the row to the bottom of the ALL sheet
and then sorts all the data in ALL. So, unless there are blank cells in
column 1, there should be no overwriting. The numbers in column B are not
involved in what the macro does.

If there will or could be blank cells in column 1, then the macro will need
to be modified to find the bottom of the data another way (perhaps a column
that won't have blank cells).

This thread it getting pretty far back in the stack, so if you want furher
help from me, you probably need to contact me directly with a sample
workbook and the below explanation of what you want to do.

[email protected]

--
Regards,
Tom Ogilvy





"Spreadsheet" <[email protected]> wrote
in message news:[email protected]...
>
> Thanks a bunch Tom, the macro is functional now.I just have a few
> concerns.
>
> 1) Right now the macro adds the selected row to the "ALL" worksheet
> according to the number in column B. This works very well when the new
> row to be added to the ALL sheet has a number in column B that is
> greater than any number in column B of the ALL sheet (the new row is
> then added to the bottom of the sheet). However, when the new row to be
> added to the ALL sheet has a number in column B that is less than some
> of the numbers in column B of the ALL sheet, the macro finds the right
> place for the new column and then overwrites whatever was currently in
> that place on the ALL sheet. This may occur because there could be many
> rows with the same number in column B.
>
> SOLUTION? Can the macro insert a new row and then add the new
> information to it?
>
> 2) The oonly other issue is this: If I want to merely update a row that
> already exists, I don't want this row to be added to the ALL sheet, I
> just want the information to be updated.
>
> SOLUTION? The runs a logical test to see if the number in Column C
> (this number is unique) of the currently selected row exists on the ALL
> sheet in Column C. If it does, then the row containing this number is
> overwritten by the currently selected row. If the number does not exist
> in Column C of the ALL sheet, the macro proceeds to insert a row in the
> correct place and writes the information there.
>
> Are these suggestions possible? I'm not a programmer so I don't know if
> that would be easy or not, but it seems like it shouldn't be impossible.
> Thanks again for all the help.
>
>
> --
> Spreadsheet
> ------------------------------------------------------------------------
> Spreadsheet's Profile:
http://www.excelforum.com/member.php...o&userid=34730
> View this thread: http://www.excelforum.com/showthread...hreadid=551134
>