I want to create a sheet that is a subset of another sheet 1

[LKAlbert]

I am trying to create a workbook containing multiple sheets. The first sheet contains all the data. The second and subsequent sheets contain all the rows for which the text entry in a specific column in each row is equal to a specific value (or contains a specific code embedded somewhere within the text of the cell), with each subsequent sheet having a different specific text values (or not containing a specific embedded code) in a specific (perhaps different) column. The idea is that once the database in sheet one is changed, automatically the reports that are needed against that database are created and all one has to do is go to that sheet and print.

Most users of this workbook are even less experienced than I am, so you can imagine how inexperienced they all are. LOL

Thank you so very much if you can help me with this problem.

Albert

 

[SkipVought]

Hi,

On each report sheet, insert an MS Query to the Source Data Sheet via Data/Get External Data/New Database Query/Excel files -- YOUR WORKBOOK -- YOUR SOURCE DATA SHEET... Edit the data in MS Query. Insert you Criteria. return data to Excel.

You can use the Worksheet_Activate event to do a Refresh of the query.

Private Sub Worksheet_Activate()
  Activesheet.Querytables(1).Refresh
End Sub

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[LKAlbert]

WOW! Though everything is not yet working perfectly, you sure made something exceptionally difficult for me only moderately so. So far all the fields are not showing up but I just started and I can see that your response was right on! Thank you so very much. Is this a sensible approach I have taken?

Albert

 

[SkipVought]

Is that a rhetorical question?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[LKAlbert]

No, it was not rhetorical. LOL
I am just trying to make sure that my approach to creating multiple "reports" automatically was the best way of going about it.

Setting up each report by using the Query function works well(except that the columns do not necessary come across in the smae order they were in the database even though I set it up to do so but a manual fix is easy. Changes to the data are also accurately reflected in each report.

The most perplexing problem is that the VB macro code that you provided does not execute automatically. When I click into the sheet within the workbook that contains the report, I have to click "refresh the data" after I insert my cursor into any element of that report. Not a really big deal, but if I give this whole database to someone else to administer at some point, I am afraid that they will not know what to do or forget to do it and the report will be incorrect.

Any more suggestions?

 

[SkipVought]

that code has to be in each WORKSHEET OBJECT that has a query table -- not in a module.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}