spreadsheet to report

[Joanne]

I am using Winxp pro, MS Office 2003

I have a workbook with about 30 sheets.
All sheets are formatted the same.
All sheets have columns for the following:
Item # Item Description Level 1 Level 2 Level 3 Level 4
The number of rows on the sheets is variable.

I would like to create a report on any one of these sheets using the
Item #, Item Description and only one of the 'Level' columns (not always
using any particular 'level' - this will be changing for each report).

It would need to contain all the rows on the particular sheet, again the
number of rows on any given sheet is not consistent so I would need a
way to tell excel how many rows to pick up.

I know that I could use copy and paste to accomplish this, but I would
like to automate the creation of this report, maybe using a parameter
type input box like those used in Access so the user can enter the range
for number of rows and the general columns, and then add the address for
the 'level' column to add to the report. Then these ranges would be
dropped onto a template for emailing to the customer. All of this would
live behind a button I will add to the toolbar.

I am much more familiar with access than excel, so while I know this
could be done in access, I am not sure if this is a good approach in
excel, and if not, what would be a good way to go about getting this job
done. I do know that there is a program called Crystal Reports, but I
don't have access to it, so I can build this simple template myself if I
can just get the info automated and dropped onto the template for the
report.

Any help or advice you can give me on this project sure would be
appreciated. As always, I bow to your experience and expertise.

Joanne

 

[Earl Kiosterud]

Joanne,

You don't say if the 30 sheets have the same items in them or not. Either way, combining
them into one big sheet will simplify matters, at least from the selection of whatever
category the sheets are. I'd read "Data across multiple sheets" at
www.smokeylake.com/excel/excel_truths.htm and consider it carefully (try to resist rejecting
it out of hand). This won't address selecting the Level, but that could be done with a
button-driven macro that simply hides the unwanted level columns.

You don't say how you want the data presented. If it could be temporarily in the same
sheet, perhaps long enough to print or copy somewhere, this would work well. If you need
the output to exist separately from the original data, then it will probably require an
Advanced Filter or something, which would have to be driven by a macro if it's to be
automated. Say more about the presentation of the output. And thow in where the desired
parameters (Level, and sheet desired) will be.
--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------

 

[Joanne]

Thanks for your time in considering my problem

I read the article at smokeylake. In fact, I read them all - very
interesting and informational.

In this particular case, being on separate sheets is okay as I will only
be using one sheet for each particular instance of the job.

I was thinking after reading your post and the above information that
maybe running autofilter on the active sheet to hide what I don't want
in the report could work, then select the rest of the sheet, copy it and
drop it on my template and print it out to present to the customer.

I would want to choose only the body of the report, not titles and there
will be no totals, then hid all of the 'level' columns I don't need;
then drop my selections onto a template (form) created just for this
process, print it and be done. I don't know much about autofilter, but I
sure can read up on it to see how this works. Then I could use a macro
or some lite ;-) coding to automate the process as much as possible if
necessary.

Any suggestions where to start reading about auto filter would be very
helpful

Thanks again Earl
Joanne

 

[Earl Kiosterud]

Joanne,

Since you want all the rows of one sheet (but only one of the Level columns), Autofilter
will not be useful. It hides rows that you don't want, but doesn't hide columns.

If you want to automate the process of hiding all but one of the Level columns, do this:

Press Alt-F11. This will take you to the VBE.
View - Project Explorer (to ensure the Project Explorer pane is visible).
Find your project (your workbook) in the Project Explorer.
Right-click anywhere in it, and choose Insert - Module.
Copy the following and paste it into the new module four times.

Sub Level1()
' Hides all but the desired Level column.
Dim i As Integer
Const LevelColumnFirst = 3 ' column of first Level column
Const LevelColumnsCount = 4 ' count of Level columns
Const Level = 1 ' level to be not hidden (for printing)

For i = LevelColumnFirst To LevelColumnFirst + LevelColumnsCount - 1
If i - LevelColumnFirst + 1 = Level Then ' is this the level we want
Cells(1, i).EntireColumn.Hidden = False ' yes, unhide it
Else
Cells(1, i).EntireColumn.Hidden = True ' no, hide it
End If
Next i
End Sub

In the second one, change Level1 to Level2, and change Const Level to 2. Repeat for the
third and fourth subs, setting them to 3 and 4 respetively. Ensure that LevelColumnFirst is
in fact column 3 (the Level 1 column) in your sheets, and if not, change it accordingly.

Copy/Paste this to the end of the module:

Sub LevelsAll()
' Unhides all the levels columns
Dim i As Integer
Const LevelColumnFirst = 3 ' column of first Level column
Const LevelColumnsCount = 4 ' count of Level columns
For i = LevelColumnFirst To LevelColumnFirst + LevelColumnsCount - 1
Cells(1, i).EntireColumn.Hidden = False
Next i
End Sub

Now Alt-Tab back to Excel. Use Tools - Macro - Macros to run each to see if it works. If
they do, you can set up buttons to click or keyboard shortcuts to press to start the macros.
Post back for more on that.