Drop-down list - process automatically.

  • Thread starter Thread starter Jeff Smith
  • Start date Start date
J

Jeff Smith

Hi I have a spreadsheet that analyses data about products.

Presently, I use a drop-down list to select the product (which is filtered,
copied, paste special - values to a new sheet) and then the data is
presented in analysis form in anoth spreadsheet that "looks" at that data..
I print the report and then select the next product. etc.

he drop-down list contents can vary but is in the format [All], [Top 10],
[Custom], Product A, Product B, Product C, etc. I always select individual
products from top of list through to bottom of list..

My question is can this process be automated using VBA?

I'd like to complile a single report for all products, for printing. Can
anyone point me to an Excel help site or provide some code for me to
overcome this problem?

TIA,

Jeff Smith
 
Have you considered using a pivot table for the report? There's
information in Excel's Help, and here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Add the product field to the page area of the pivot table.
Then, you could use a macro to print each product. For example:

'===========================
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveSheet.PrintPreview 'use this for testing
Next
Next pf
End Sub
'================================

Jeff said:
Hi I have a spreadsheet that analyses data about products.

Presently, I use a drop-down list to select the product (which is filtered,
copied, paste special - values to a new sheet) and then the data is
presented in analysis form in anoth spreadsheet that "looks" at that data..
I print the report and then select the next product. etc.

he drop-down list contents can vary but is in the format [All], [Top 10],
[Custom], Product A, Product B, Product C, etc. I always select individual
products from top of list through to bottom of list..

My question is can this process be automated using VBA?

I'd like to complile a single report for all products, for printing. Can
anyone point me to an Excel help site or provide some code for me to
overcome this problem?

TIA,

Jeff Smith
Click to expand...
 
Debra,

Thank you for repying.
My list of product data is about 1000 records. Each record contains the
elements that are analysed. Analysis involves calulating mean strength,
standard deviation, coefficient of variation, within-test coefficient of
variation and comparing these calculated results with pre-determined values
in a lookup table.

I need to do the analysis, copy and paste the KPI's into a report then bring
up the next record set and repeat the exercise ... until all unique products
are completed.

I understand a pivot table would rearrange the data but the necessary
calculations would not be done. I was hoping there is a VBA way of statitng
from the first record to the last record do this.

regards

Jeff Smith

Debra Dalgleish said:
Have you considered using a pivot table for the report? There's
information in Excel's Help, and here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Add the product field to the page area of the pivot table.
Then, you could use a macro to print each product. For example:

'===========================
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveSheet.PrintPreview 'use this for testing
Next
Next pf
End Sub
'================================

Jeff said:
Hi I have a spreadsheet that analyses data about products.

Presently, I use a drop-down list to select the product (which is filtered,
copied, paste special - values to a new sheet) and then the data is
presented in analysis form in anoth spreadsheet that "looks" at that data..
I print the report and then select the next product. etc.

he drop-down list contents can vary but is in the format [All], [Top 10],
[Custom], Product A, Product B, Product C, etc. I always select individual
products from top of list through to bottom of list..

My question is can this process be automated using VBA?

I'd like to complile a single report for all products, for printing. Can
anyone point me to an Excel help site or provide some code for me to
overcome this problem?

TIA,

Jeff Smith
Click to expand...
Click to expand...
 
Record a macro as you use an Advanced Filter to extract a list of unique
products.
http://www.contextures.com/xladvfilter01.html

Sort the list, then loop through it, and extract each product to the
calculation sheet. An Advanced filter will automatically extract values,
rather than formulas.

The sample workbook here, named 'Update Sheets from Master' might give
you some ideas:

http://www.contextures.com/excelfiles.html#Filter


Delete the list of products

Jeff said:
Debra,

Thank you for repying.
My list of product data is about 1000 records. Each record contains the
elements that are analysed. Analysis involves calulating mean strength,
standard deviation, coefficient of variation, within-test coefficient of
variation and comparing these calculated results with pre-determined values
in a lookup table.

I need to do the analysis, copy and paste the KPI's into a report then bring
up the next record set and repeat the exercise ... until all unique products
are completed.

I understand a pivot table would rearrange the data but the necessary
calculations would not be done. I was hoping there is a VBA way of statitng
from the first record to the last record do this.

regards

Jeff Smith

Have you considered using a pivot table for the report? There's
information in Excel's Help, and here:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Add the product field to the page area of the pivot table.
Then, you could use a macro to print each product. For example:

'===========================
Sub PrintPivotPages()
'prints a copy of pivot table for
'each item in page field
'assumes one page field exists
On Error Resume Next
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveSheet.PivotTables.Item(1)
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields(pf.Name).CurrentPage = pi.Name
' ActiveSheet.PrintOut 'use this for printing
ActiveSheet.PrintPreview 'use this for testing
Next
Next pf
End Sub
'================================

Jeff said:
Hi I have a spreadsheet that analyses data about products.

Presently, I use a drop-down list to select the product (which is
Click to expand...
filtered,
copied, paste special - values to a new sheet) and then the data is
presented in analysis form in anoth spreadsheet that "looks" at that
Click to expand...
data..
I print the report and then select the next product. etc.

he drop-down list contents can vary but is in the format [All], [Top
Click to expand...
10],
[Custom], Product A, Product B, Product C, etc. I always select
Click to expand...
individual
products from top of list through to bottom of list..

My question is can this process be automated using VBA?

I'd like to complile a single report for all products, for printing.
Click to expand...
Can
anyone point me to an Excel help site or provide some code for me to
overcome this problem?

TIA,

Jeff Smith
Click to expand...
Click to expand...
Click to expand...
 
Back
Top