Looping a macro to build a report

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
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 (n) (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 (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) 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 (n) 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 (n) and then rerun macro (m1).

What I would like is another macro (m2), which automatically loops through the different values for (n) 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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,812
Code:
Option Explicit
Sub CreateMultipleReports()
    'Add another worksheet with headers
    'A1 = "Selected"
    'B1 = "Property"
    'C1 = "Drop Down Index"
    'Any mark in Column A when a property name is in column B
    'of the same row will update the dropdown target cell with the
    'the name in column B and run the report macro.
 
    'If you need to use the index value in the dropdown
    'change 2 to 3 in the rngDropDownTarget = Me.Cells(lX, 2).Value
    'line
 
    Dim lX As Long
    Dim lLastRow As Long
 
    Dim rngDropDownTarget As Range
 
    Set rngDropDownTarget = Worksheets("B").Range("C14")
    lLastRow = Me.Cells(Rows.Count, 2).End(xlUp).Row
 
    For lX = 2 To lLastRow
        If Len(Me.Cells(lX, 1).Value) And Len(Me.Cells(lX, 2).Value) > 0 Then
            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
 
Private Sub cmdSelectAll_Click()
    Dim lX As Long
    Dim lLastRow As Long
 
    lLastRow = Me.Cells(Rows.Count, 2).End(xlUp).Row
    Range("A2:A" & lLastRow).Value = "X"
End Sub
 
Private Sub cmeSelectNone_Click()
    Dim lX As Long
    Dim lLastRow As Long
    lLastRow = Me.Cells(Rows.Count, 2).End(xlUp).Row
    Range("A2:A" & lLastRow).Value = ""
 
End Sub
Two subs for command buttons to select all and select none of the properties.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Where is the dropdown? Is it, say, in SheetB and cell A1? Where is the start of the source for the dropdown that chooses the building? Is it, say, 'SheetB' and cell A2, then going down from there?

Do something like this for m2:

Code:
dim i as long
i=2
Sheets("SheetC").Activate  'this is if your macro m1 requires that you be on SheetC
While Sheets("SheetB").Range("A" & i) <> ""
  If (Sheets("SheetB").Range("B" & i) = "x" Then
    Sheets("SheetB").Range("A1") = Sheets("SheetB").Range("A" & i)
    Call m1  'this is the macro you described and named
  End If
  i=i+1
Wend
...If you want the tick boxes, add a column immediately to the right of your building list and un-comment the if loop start and end lines in the code above that are for that.

Hope that works :p
Tai
 

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
Guys, thank you so much for your suggestions. Although MrExcel has always come up trumps for me in the past, I must admit that with this query I thought I might just be asking too much, so thank you both for your suggestions. I shall be going away and trying the above out and will revert back as soon as I can.
Thanks again.
 

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
Phil
Thanks a million for submitting your suggestion, but I'm sorry to say it didn't work - though that may well be because of my incompetence implementing it. I immediately had an issue with the first line 'Option Explicit' which appeared to be because I copied your code into an existing module after some other subs. Moving it to the top sorted that, but the then I ran into other problems which I couldn't begin to decipher as this code is well above my usual capability. In the meantime I received a solution from taigovinda which does seem to do most of what I want, but I would still like to understand what yours does so I will persevere with it. if I give you precise error messages, would you be able to offer further help?
Many thanks.
 

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
Tai
Thanks a lot for your solution, which seems to work a treat and is saving me a whole load of time.
I do have a further request. At the moment I need to go into my setting sscreen to put crosses against the properties I want to include in the report. That's fine for me, but I want to hide quite a few of the working sheets, including the settings sheet, when I let my colleagues loose on my baby. Is there any way I can get a dialog box to pop up when I run the macro showing a tickbox for each of the available properties so that the user can then tick which properties he wants before the macro continues? Ideally with a 'select all' and 'select none' shortcut button?
The list of available buildings is in a sheet called 'Global Settings' in cells B2:C101, where column B contains the ticks and columns C contains the property name.
Many thanks.
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
I'm going to be not so helpful and say that: yes, you can create a "dialogue box". If you open the VBA editor (Alt+F11) you can create a userform where you place a checkbox for each property needed.
You had a hundred, right, so manually that might be something of a task, but I'm hoping this can be coded as a macro as well. Though I don't know the code to do so.
 

ShogunPatch

Board Regular
Joined
May 8, 2009
Messages
52
Thanks Snowblizz; hopefully somebody out there will be able to point me in the right direction.
 

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
Hi,

I don't know how to do all of that in a dialogue box. Many on here can probably tell you how, but that sounds like it is beyond my capability at this point.

If I were doing this, I would give a list of the properties on an Excel sheet and have them place X's, the way you are doing. I would hide any settings I don't want the user to see, and protect all of the cells in the sheet except for the cells that are supposed to contain the tick marks.

Hope that helps.

Tai
 

Watch MrExcel Video

Forum statistics

Threads
1,101,800
Messages
5,482,973
Members
407,371
Latest member
gdjenkins80

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top