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.
 

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
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top