Looping a macro to build a report

[ShogunPatch]

OK, forgive me for being overly verbose here. I will try and keep this brief but want to include all the info anybody out there with the right knowledge might need to help solve my problem; as such I am going to explain my issue as I would to a five year old - as much for my own sake as for yours!

I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.

One of the settings in one of the sheets (B) dictates which building is currently being looked at. As such, sheet (C) effectively builds a report for building and so can report on just one building at a time, that building being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).

If I want to get a consolidated picture for all buildings I currently manually change the building choice in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).

I then need to manually change the selection and then rerun macro (m1).

What I would like is another macro (m2), which automatically loops through the different values for so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.

I realise this may be asking a lot, but any help would be much appreciated.

 

[pbornemeier]

Option Explicit sets a compiler option that requires all variables to be dimensioned before they are used. It is kind of like seat belts in a car - protectes you if something goes wrong (misspelling variable).

If you have any questions on my code, I'll be happy to reply

 

[ShogunPatch]

Hi Phil, that's a very kind offer which I will gladly take you up on, though your code is so far beyond my level of ability that you may regret the offer!

Perhaps before worrying too much about understanding in detail what your code does, I should focus on getting it to work because I'm not sure I have been able to get it all to work so far. This is what I have done:

First attempt: I copied your code directly into an existing module (Module 1) which is where all my other macros are, initially after my last sub but then inserted before the first one as I got a message saying I could have nothing before 'Option Explicit'. I then got an error message saying something along the lines of 'Compile error: invalid use of Me keyword'.

Second attempt: I then had a brainwave and tried moving your code into the sheet I have created per your instructions (by right clicking on the name of the sheet and selecting View Code). That's probably a really obvious, basic thing to do, but I've never really been clear on where to save macros so I generally just record them to start with and then do any editing/adding wherever they have been recorded to. After a bit of messing about that now seems to work insofar as it loops through any properties that have an 'x' against them before I run the macro and produces the report, but I have to have selected my properties beforehand. Is it supposed to give me a dialog box or something that allows me to select which properties to include after I start running the macro? I sort of assumed it was meant to as you seem to have added a couple of routines to select all and clear all, but maybe I've misunderstood.

If not, is it possible to add that? I have been desperately searching online for a way to do this (I have even started a separate thread here on MrExcel but so far no one has responded to it). I found an interesting article here
http://www.eng-tips.com/faqs.cfm?fid=110
which I suspect may have helped if I'd had a better understanding of VBA to begin with but it is so advanced (to me!) that I'm struggling to make use of it.
Many thanks, I really appreciate your help with all this Phil.

Patch

 

[pbornemeier]

The code I posted earlier requires that you select the properties that you want to process before starting the code.

The select all and clear all code is used to add or remove marks from column A of the worksheet I asked you to add.

Using the Me keyword in code that is in a worksheet code page is the same as using Worksheets("WorksheetName").
Using the Me keyword in code that is in a userform code page is the same as using UserForm("UserFormName").
Using Me in a normal module confuse the compiler and generates the error message you mentioned.

Code on a codepage for a worksheet should only be manipulating or working with stuff on that worksheet.
Code on a codepage for a userform should only be manipulating or working with stuff on that worksheet.
Properly composed code on a normal module can refer to any other module, any worksheet, or any userform.

Here is the code again with a few more comments:

Sub CreateMultipleReports()
    'Add another worksheet with these headers the name of the
    '    worksheet is not important
    'A1 = "Selected"
    'B1 = "Property"
    'C1 = "Drop Down Index"
    
    'Column B should contain the same info as the drop down
    'Column C should contain the index number for each item
    '    (starts with 0 or 1 and increments by 1)
    
    'Put this code on the codepage for that new worksheet
    
    'When this code is run, if there is any characters in
    'in Column A when a property name is in column B, the
    'drop down target cell (defined below) will be updated
    'with that value and the report macro will run.  This
    'will occur for each row that has a mark in column A.

    
 
    Dim lX As Long
    Dim lLastRow As Long
 
    Dim rngDropDownTarget As Range
    'Next line should be changed to the sheet/cell that your drop down
    'changes when a different value is selected in the drop down
    Set rngDropDownTarget = Worksheets("B").Range("C14")
    
    lLastRow = Me.Cells(Rows.Count, 2).End(xlUp).Row 'Finds last row in column B
 
    For lX = 2 To lLastRow
        If Len(Me.Cells(lX, 1).Value) And Len(Me.Cells(lX, 2).Value) > 0 Then
            'If you need to use the index value in the dropdown
            'target cell instead of the value of the dropdown,
            'change 2 to 3 in the next line
            rngDropDownTarget = Me.Cells(lX, 2).Value
            'Replace next line with report macro name
            Debug.Print Me.Cells(lX, 2).Value
        End If
    Next
 
End Sub

I will work on creating a dialog box with 100 check boxes (can there be more? or less?, if so, what is the possible range). with names from the worksheet I described earlier. Would you want these names sequentially placed in the dropdown target cell as the macro runs?

Would it be useful to be able to select all that contained a particular string, something like red* ?

The dialog box is possible, but it may be tough to use because it will reset to its default conditions each time the dialog box is open (unless additional provisions are made to preserve the selections).

 

[ShogunPatch]

Hi Phil, thanks for your comprehensive reply. WOW! I am learning so much!!

At present my model (i.e. all the interrelating formulae, etc) is built to handle up to a maximum of 100 units. It could potentially be amended to handle more but to be honest the workbook is already so big that I probably wouldn't want to consider expanding it beyond these limits. As such, the range of required tickboxes is anything from 1-100 and yes, it would be useful to be able to automatically select those with a given string in the name, but not if that is going to over-complicate things code-wise.

In terms of the difficulty with the proposed dialog resetting to default conditions, as I picture it what I'm talking about is a dialog box which effectively reads from/writes to a table, where A2-A101 are blank or have 'x' in them, and B2-B101 contains unit names. Is it not just a case of ('just a case of' he says... ha!) the tick boxes representing column A and reading from it, so when the dialog box is created, if A2 has a cross in it the tickbox is already ticked, and if A2 is blank the tick box is unchecked? The labels for the the tick box are read from column B. If, say, when the dialog box opens, A2 is blank then the tick box is blank; if I then tick it and continue that then puts an 'x' in A2 (required so that unit is included in the report). When I next run it, A2 already has an 'x' in it so the tick box is checked. If I uncheck it and continue it then clears the 'x' from A2, etc. I don't know if any of that makes sense to you, but in theory it all seems so simple to me!

I hope you don't end up wasting too much of your time on this, but do really appreciate all your help, many thanks.

Patch

 

[pbornemeier]

What would be the widest name for a property? I am picturing a grid 5 across and 20 down, but I have to know the longest name to account for spacing when building the form. If there is a short form of the name of longer properties, they could be in an additional column to be used to put the short names on the userform.

Each checkbox on the form can easily take its initial state from column A and make changes to column A as it is changed. I believe you could get the same effect by allowing users to access the worksheet with all cells but the ones in column A that could contain the X protected so they could not be changed.

 

[ShogunPatch]

The property names are freeform and there is currently no limit on size; in practice the longest name is probably about 30 characters long at the moment, which is clearly too much for the form in question. That said, I have already implemented a shortform name which I use for other parts of the workbook. Again, I have not implemented any hard and fast rule as to length, but could easily limit it to, say, 8 characters.

Re-reading your last paragraph, however, you are of course right and that is effectively what happens at the moment. The reason I had thought of a dialog box was that (a) I thought it would look more professional in terms of providing a user front end and (b) in practice I haven't built a separate sheet as you suggested because the required table already exists in my 'Global Settings' worksheet, but that has lots of other settings I not only don't want other people to change, but also don't want them to see. The more I think about it, however, the more I'm getting the impression it is possibly not worth the hassle of setting it up to work via a dialog box. Instead, perhaps I should amend the existing macro so that it opens up my 'Global Settings' sheet (which I could amend to lock and hide all the other stuff as you suggest), then pause so that the user can check the units he wants before clicking a button to continue the macro. I hoped it would be possible to programmatically generate the dialog box to be the correct size and with the right number of tick boxes to suit individual circumstances but it sounds as though I was dreaming on that one. This revised way users would have just one long list and they could scroll down the worksheet if need be. This raises a whole set of new questions:

1/ Typing in an 'x' isn't very pretty: how easy would it be to put checkboxes on the actual worksheet which could be ticked or unticked in lieu of typing in an 'x'
2/ How do I get my macro to pause once its called up the sheet so that the user can make his selection?
3/ Presumably, in addition to the tickboxes I would need to have some kind of command button to resume the macro once the selection had been made, and probably also another to cancel the macro altogether if need be. How do I set these up?

Many thanks.

Patch

 

[pbornemeier]

Make a new worksheet that only contains the values you want people to see. On the 'Global Settings' worksheet, put formulas that take their values from the corresponding cells on the new worksheet.

Although typing an 'X' is not pretty, it IS fast and you can drag a typed X down onto subsequent rows quite quickly. You can't do this with the checkboxes

You don't have the macro pause, you break it in to two pieces:
The first part does all of the preliminaries then displays the worksheet then stops.
The second part is initiated by the user clicking on a button on the workksheet named "Continue" or something like that.

Something like this:
http://spreadsheetpage.com/index.php/tip/displaying_a_menu_of_worksheets_to_print/
could be used as a model to create the form you want, but with 100 entries on it, it would get quite large.

Here:
http://articles.techrepublic.com.com/5100-10878_11-1041101.html
is an article about putting checkboxes on the worksheet.

Let me know which way you want to go with it. I can give you an assist.

 

[ShogunPatch]

Thanks for the various links; the first one was of course particularly interesting. At least now I know it is possible to create a dialog box on the fly, albeit doing one that does what I want is probably still out of my reach.
In any event, I think you've convinced me to go with crosses on an input sheet for now as that is clearly a lot easier to achieve and I do agree with you about the ease of quickly filling in Xs where you want them.
Thanks.

 

[pbornemeier]

Put this code on the same sheet as the previously posted ones. Link it to a command button (Select Like) on that page. It will select the rows (X in column A) that have a partial match to the input. Coupled with Select All, Select None, it should help mark the items you want to process pretty quickly.

Private Sub cmdSelectLike_Click()
    Dim lX As Long
    Dim lLastRow As Long
    Dim sAnswer As String
    
    lLastRow = Me.Cells(Rows.Count, 2).End(xlUp).Row
    
    sAnswer = UCase(InputBox("Enter the string to match to part of the property titles you want to select", "Enter String", ""))
    If Len(sAnswer) > 0 Then
        For lX = 2 To lLastRow
            If InStr(UCase(Me.Cells(lX, 2).Value), sAnswer) > 0 Then
                Me.Cells(lX, 1).Value = "X"
            End If
        Next
    End If
 
End Sub