Trying to create a summary page

[Xaos]

Hello all.

I work in a theme park and I have a spreadsheet that has status of effects broken down by area. Each page is a seperate area. Column B on every page lists the effect status (Either OOS, or In Service).

I would like to add a summary page that will search the rest of the pages and any which column B has 'OOS' in it, add the whole line to the list on the summary page (So I can have a summary of all effects that are out of service).

Is there an easy way to do this?

Any help appreciated.

[protonLeah]

That will take a macro but you will have to post a sample workbook.

[xladept]

Hi XAOS,

Try on a copy of your workbook:

Please Login or Register  to view this content.

[xladept]

Correction - try this rather than the first posting:

Please Login or Register  to view this content.

[Xaos]

Awesome I will try this when I get to work tomorrow. Can I set it up to auto-run as soon as I open the workbook?

[royUK]

In a standard moduke

Please Login or Register  to view this content.

In the workbook open event

Please Login or Register  to view this content.

[xladept]

As Roy says:

Private Sub Workbook_Open()
OOS
End Sub

[Xaos]

Correction - try this rather than the first posting:

Please Login or Register  to view this content.

This worked, but gives me a run-time error '9': subscript out of range.

When I debug it highlights this line:

Set WS = Worksheets(j)

Also how Do I clear the old data before it runs? I noticed it keeps adding the lines each time I open the workbook.

One more minor thing, I have conditional formatting set on the other worksheets. Is there a way to force it not to copy the fill color when it copies over to the summary page?

Thank you very much for taking the time to help me out. You have no idea how much time this will save and increase productivity.

--edit-- I should have reported that I am using excel 2003 SP3 at work if that helps.

[xladept]

I'll look at the index and let you know. Is the summary the last sheet???

[Xaos]

I was about to say yes it is the last sheet (as displayed in tab order), but looking in the VBA editor, it was not. It is a moot point, however, because I switched it to be the last sheet and it still caused the same error.

--edit-- I commented out this part:

If Worksheets(j).Name = "Summary" Then j = j + 1


and the error went away and the script functioned except that it added all the lines twice for some reason.

[xladept]

Hi Xaos,

Try this version:

Please Login or Register  to view this content.

[Xaos]

Hi Xladept

This code appeared to do nothing at all. I can e-mail you a copy of my workbook if you like, but I can't post it publicly due to confidentiality of my workplace.

There are 7 worksheets with Summary being the last (sheet 7).

This is what I currently have on the "This workbook" page of VBA editor:

Please Login or Register  to view this content.

--Edit--

I went ahead and built a dummy workbook just to give you an idea of how it is setup.TestbookA.xls

[Xaos]

OK I figured out what the problem was. I had the first 3 rows merged with a title on each page. The script you provided worked on my test workbook. The only odd thing now is that the title row (row 1) from sheet 6 is being copied over to the summary page. Other than that it is working perfectly.

--edit-- figured out how to fix.

I commented out this line:

Worksheets(Worksheets.Count - 1).Cells(1, 1).EntireRow.Copy Summary.Cells(1, 1)

and added Summary.Cells(1,1) = "Summary"

[xladept]

Hi Xaos,

Great going- I thought that there would be header rows. Sorry about that. (They say a mathematician will assume anything but responsibility.)

Make that be:

Please Login or Register  to view this content.

[Xaos]

Thanks for all your help. Much appreciated. Added and modified a few things, but here's my current code version:

Please Login or Register  to view this content.

The autofit stuff doesn't seem to work, but ill figure that out through online searching.

Thanks again.

[xladept]

I thought we were finished - what more do you need?

[xladept]

The autofit needs to be run after the sheet is populated - so if you move it to the end it should work!

[Xaos]

Yeah nothing more needed. Thanks a lot for all your help. I've actually learned a lot from disecting your code. Appreciate it.

[xladept]

You're welcome!