First Project - nearing completion

[rrtts]

@md, xld and any others,

I appreciate all your help to this point...with your expert assistance I have the basic code and foundation to accomplish this project for work...pretty much all that is left is the cosmetics and fine tuning.

I have attached a sample of the worksheet that displays some of these concepts. In a nutshell, my organization receives an email several times a day that is called a database verification. Essentially, it provides a unique routing indicator (RI) and a unit name used to determine where the unit is and how they are receiving email.

Previously, one of my operators would have to read the contents of this email and manually generate a report to verify that the RI and unit name were associated properly. With your help and the magic of excel, this report can now be generated automatically simply by cutting and pasting it into excel and clicking a button.

I do have one small favor...

First of all the Reset Page is just to simulate the action of cutting and pasting the text into the spread sheet (there are actually many more units and categories). It will be deleted in the final product. Also, sheet 2 is hidden. Although it kind of duplicates the data on sheet 3, I intentionally did this because I will also use sheet 2 at a later date for other functions.

Anyway, the help I need is on Sheet 3 - the DB Summary sheet. I'm not quite sure how to explain what I am trying to achieve, but I think if you could please take a look at it, it will be kind of obvious. Currently, I have just used the paste special - paste link function under each category to display the data. This works but has a few inefficiences. For one, it requires more space even if some of the data comes back as blank. The report I generate needs to be limited to a single page, and although it's not shown in this worksheet, I have more data to squeeze on the page.

Ideally, each of the categories would be listed and the data under each category would be spaced out depending on the number of entries. Sort of like at the end of the last row of data in category 1, skip a space and start category 2...and so on and so forth.

I don't know how to do this (if it is even possible) because the results of these columns are all formula/array driven.

Any help with this problem or any other feedback in general would be greatly appreciated.

[acw]

Hi

This is a bit clunky but see if it helps. Put it into sheet DB Summary event area



Private Sub Worksheet_Activate()
application.screenupdating = false
Cells.ClearContents
Cells.ClearFormats

Range("A1").Value = "DATABASE VERIFICATION SUMMARY"
Range("A1").Font.Bold = True
Range("A1").HorizontalAlignment = xlCenter
Range("A1:G1").MergeCells = True

Range("E2").Value = "RAYDAY"
Range("E2").Font.Bold = True
Range("F2").Formula = "=TODAY()-DATE(YEAR(TODAY()),1,0)"
Range("F2").Font.Bold = True

With Range("A4")
.Value = "CAT 1"
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Sheets("db results")
.Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Copy
End With
Range("A5").PasteSpecial xlPasteValues

With Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
.Value = "CAT 2"
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Sheets("db results")
.Range("B2:B" & .Cells(Rows.Count, 2).End(xlUp).Row).Copy
End With
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

With Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
.Value = "CAT 3"
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Sheets("db results")
.Range("C2:C" & .Cells(Rows.Count, 3).End(xlUp).Row).Copy
End With
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

With Range("G4")
.Value = "CAT 4"
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Sheets("db results")
.Range("N2:N" & .Cells(Rows.Count, "N").End(xlUp).Row).Copy
End With
Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

With Cells(Rows.Count, 7).End(xlUp).Offset(2, 0)
.Value = "CAT 5"
.Font.Bold = True
.Font.Underline = xlUnderlineStyleSingle
End With
With Sheets("db results")
.Range("O2:O" & .Cells(Rows.Count, "O").End(xlUp).Row).Copy
End With
Cells(Rows.Count, 7).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Range("A1").Select
application.screenupdating = true

End Sub


Basically every time you select the sheet it will rebuild the data.

I don't know what you have in the source sheets, but it is bringing in what seem to be blank cells. Perhaps a hangover from the source data? Needs to be reviewed if you want to save size.

HTH

Tony

[mdmackillop]

Are you limited to these 5 , 6? categories which refer the these specific columns, or are there more which may need to be be included on the Summary Page.

[rrtts]

@ acw - thanks a lot...I'll give it a shot.

@ md - 20 categories total.

[mail2bharath]

coollllllllllllllllll