Copy data based on multi criteria to another sheet and hide rows using range on sep sheet

Hello,

I am very new to Excel and cannot get my head around macros. It doesn’t help that I am also blonde!

I am using MS Excel 2003.

I understand that my approach is probably not the most efficient, but given that I have only tonight to solve this issue I guess I am stuck with it for now. Any assistance you could give me would be very much appreciated.

Originally what I had been doing which worked quite well was:

On sheet 2 “Data Entry” I had a column called ‘Check’ (column D) which contained the following formula:

=COUNTIF(E7:BB7, "<>")

This returned an incrementally increasing value if there was data in the row.

The initial macro I wrote copied the entire sheet (which I renamed Report) and then ran a this little gem of a macro (which I found here) which hid the rows in Column D which contained a ‘0’ whilst leaving the headings (blue fill)

Sub Hiding()
Dim HideRange As Range, c As Range
Columns(1).EntireRow.Hidden = False
Set HideRange = Range("d6:d" & _
Range("A65536").End(xlUp).Row)
For Each c In HideRange
If c.Value = 0 Then c.EntireRow.Hidden = True
Next c
End Sub

The initial problem I could not solve by myself was how to divide this up on a monthly, quarterly or annual basis.

So I created the attached spreadsheet and as the check column now required more than just 1 column and it wouldn’t fit on a single spreadsheet, I created a ‘master’ validation sheet which contained the relevant formulas for the monthly, quarterly and annual splits across two main areas ‘theme’ – info in the drop down boxes or text in the w/ending month columns and ‘audience’ (located in blocks at the end of each month and corresponds to an entry in the theme section – or should).

The key elements from the client’s perspective is that they wanted to be able to run a report for ‘theme’ and ‘audience’ for a single month (I was thinking current month) or for a quarter (starting from July 12) or annually, with the last option being the next 3 months from the current month.

I would then run the hide rows macro for that month based on the information in the relevant column on the master validation sheet.

That’s where I got stuck and don’t know where to from here.

I have attached a sample spreadsheet minus sensitive data with some sample reports. Only one report tab allowed at any one time.

The key sheets are:

• Hide – Validation (which will be hidden)
• Start here (not required for reporting – will link to relevant sections on the data entry sheet using a macro)
• Data Entry
• Publication Dates (not required for reporting – static data only)
• Generate Report
• Report (generated by macro from the Generate Report tab)

TEST Spreadsheet - Excel Help v1.xls

I am also having problems with the size of the file.

Please let me know if you need more info.

Really thank you – I mean it. Numbers are not my forte.

Suzi