High level design question - VBA beginner

[RossPhillips]

Dear all,

All comments, thoughts and advice would be most welcome!

Background Info

I recently joined a small engineering related organisation and have been asked to take over the monitoring of internal projects. Currently, an excel (2013) workbook is used with three sheets - allbeit it hasn't been updated in a while.

Sheet 1 - Full details. Has around 20 columns (e.g. title/status/stakeholders etc)

Sheet 2 - Reporting 1 - for Board. Has 6 columns in common with the first sheet, plus 7 columns which are used as a calendar (the next 7 quarters) to provide a graphical representation of when projects are meant to be reviewed by the board.

Sheet 3 - Reporting 2 - for council. Has only 3 columns (again in common) and then 7 columns for the next 7 quarters.

Currently this is meant to be populated and updated manually but as evidenced by the fact it is currently out of date, I would like to automate the process so that adding a new project or updating an existing one is easier. I would also like to improve my excel/VBA skills so it seems like a good little project to practise on.

Importantly, I have been asked to preserve the general format of Sheet 2 as it has been agreed by the Board.

The Question

I can see two main ways of doing this but before I embark on it, I thought it would make sense to ask those in know if they would have a preference and why?

Option 1 - Create one large sheet with all possible columns (20 + two sets of 7 forward quarters) and then use two macro buttons to hide columns, effectively creating the Board or Council view, which my boss could then print screen/print for presentation purposes.

Option 2 - Keep the sheets separate but create a 'populate' button on sheet 1 which will automatically re create sheets 2 and 3 in the desired format. I would need to make sure sheets 2 and 3 were read only to make sure any user updates the first sheet with any changes.

I suspect Option 1 would be the easier to implement and I could easily add more buttons for additional functionality such as hide all rows that represent completed projects. My main worry is that it will be hard to make the formatting suit all three views. My first attempt struggled because I used "merge cells" to create group headers over multiple columns, which don't work when you hide selected entire columns.

Thanks for your time and I look forward to reading your thoughts!

Cheers,
Ross

 

[Scrappy666]

Hi Ross,

If it were me i would stay away from the VBA entirely, however i'm sure others may disagree.
It sounds like this workbook is just manipulating data and placing it in different places and in different views/formats? If this is the case i would stick with my formula rather than VBA.

I can't comment on how to do this or be certain on my opinion without an example.
Could you provide an example workbook and possibly an example of your desired output?

Thanks,
Dan

 

[RoryA]

I would go with option 1. Having all the data in one table will make your life much easier. Your code can always create separate reporting sheets in any format if required (using a template sheet will probably simplify that).

 

[Norie]

How about Option 3 - Use a database, eg Access?

 

[RossPhillips]

Thanks to all for the quick responses.

I've perservered with Option 1 and it has worked pretty well. I've created three macros (below) which do the job - although probably not very elegantly. I am struggling however to write a macro that hides any row for which the Status column is not "Open". My attempt at the macro is also below - the debugger keeps highlighting the End If statement (i.e. it wont go on to the next itteration of the for each loop)

Sub Full_view()
'
' Full_view Macro
' Expands all coloumns


Sheets("Front sheet - full info").Columns("A:AH").EntireColumn.Hidden = False

End Sub


Sub Board_view()
'
' Board_view Macro
' Display a read only view of the coloumns appropriate for showing the board
'


Sheets("Front sheet - full info").Columns("A:AH").EntireColumn.Hidden = False
'Makes sure all columns are not hidden to start with - would like to change to include all columns rather than selected range


Sheets("Front sheet - full info").Columns("D:D").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("F:G").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("I:J").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("L:M").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("P:R").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("Z:AF").EntireColumn.Hidden = True

'Hides all the coloumns not needed for Board View

End Sub




Sub Council_view()
'
' Council_view Macro
' Display a read only view of the coloumns appropriate for showing the council or other Members


Sheets("Front sheet - full info").Columns("A:AH").EntireColumn.Hidden = False
'Makes sure all columns are not hidden to start with


Sheets("Front sheet - full info").Columns("D:J").EntireColumn.Hidden = True
Sheets("Front sheet - full info").Columns("L:Y").EntireColumn.Hidden = True
'Hides the coloumns not needed for Council view


End Sub


Sub Open_projects_only()




Sheets("Front sheet - full info").Rows("1:200").EntireRow.Hidden = False
'Resets the sheet by unhiding all rows - again would rather not have to manually make a selection in case no of rows grows past 200


Dim i As Range


'Finds the last row in the K column that has a value
'FinalRow = Cells(Cells.Rows.Count, "K").End(xlUp).Row - I had to comment out this as I couldnt get it to work


'For every row after row 5 (to allow headings), hide the entire row if the value of cell K is not "Open" - I simplified the code which used to use FinalRow but still can't make it work

For Each i In Range("K5:K200")
If i.Value = "Open" Then
i.EntireRow.Hidden = False
Else
i.EntireRow.Hidden = True
End If
Next


End Sub

I feel I must be making a fairly elemental error somewhere...any help or advice on how to improve would be most welcome!

Cheers,
Ross

 

[RoryA]

I'd suggest an autofilter:

Sub Open_projects_only()

    With Sheets("Front sheet - full info")
        .AutoFilterMode = False
        .Range("K4:K200").AutoFilter field:=1, Criteria1:="Open"
    End With
End Sub

 

[RossPhillips]

RoryA - I won't pretend to understand how that code works but work it did

The only slight issue is that once again I get a "Code execution has been interrupted Error box pop up and if I click on Debug, it highlights the "End With" line.

Can you tell me what the "autofilter field:=1" bit does please?

 

[RoryA]

It says you want to filter that range on the first field (in this case, the only field) with a criteria of "Open". It just automates what you would do with a manual autofilter.

I can't see any reason for the code to stop there - I suspect it's a temporary gremlin.