Automatically copy rows based on criteria

[Guest]

I have a worksheet (Data) that contains a set of criteria for evaluating a
given subject (aprox 100+ evaluation points). Each of the pieces of criteria
can have 1 of 4 different values (0-3). If the value is = 1, 2, or 3 a
comment regarding that point must be input into the column beside the value.

These criteria & their rankings are also broken down and displayed in one of
several other worksheets in the workbook to allow for easier review (say
points 1 - 20 are in worksheet A, 21-40 are in worksheet B, etc...).

My goal is to display the comments from the "Data" worksheet that are
associated with each piece of criteria on worksheet A, B, etc... without
having to reserve lines 20 lines on each worksheet (one for each possible
comment). So in short, if I have a comment for item 15, I would like to
automatically insert a row at the end of my first set of data and before my
second set of data that would display the appropriate comment in worksheet A
with out reserving a line for each possible comment

sidenote: if possibel I would like to do this with out employing a user
triggered macro

Sorry for the length of this post and thank you in advance for your time and
effort.

 

[Guest]

Jay, my first suggestion would be to use a pivot table. It automatically
expands/contracts to just the right size needed, and you can write a very
small macro that runs automatically to update it whenever the worksheet is
selected. For example, on Worksheet A, you can right-click the tab name,
View Code, and paste this in (macro from Debra Dalgleish):

Private Sub Worksheet_Activate()
Me.PivotTables(1).PivotCache.Refresh
End Sub

This brings up another point. Even if you decide a pivot table is not quite
right, macros do not have to be "user triggered." The whole point of most
macros is to run automatically when the time is right. As you see above, I
only refresh the pivot table when Worksheet A is activated, not necessarily
the moment the data was changed on the input sheet.

Hope that helps.
-KC

 

[Guest]

There is a good chance that I am doing this wrong but I can't get my commets
to appear in the Data area, it will only sum the # of comments, any
suggestions?

 

[Guest]

You don't put them in the data area...put them in the row area right along
with your criteria. To make the comment appear directly beside the criteria
(instead of in outline format, which is the default for pivots), right click
any of the criteria, select "Field Settings," and under "Subtotals" select
the radio button labeled "None."

 

[Guest]

One more thought...if you are going to have some comments that are empty, put
an empty string in those (="" works just fine). Otherwise, your pivot table
will type (blank) into your report, which is silly but it is what it is.

 

[Guest]

Thanks for your help on this