Report from tabular data

[stefanaalten]

How can I create a report from tabular data? I have a simple list (header row, records in 700 rows, field data across 17 columns). The data represents movies (title, director, year, etc.)

I would like to generate a report with each record formatted as a "card", e.g. title in bold on one line and underneath, indented, further details about the film, to produce an easy-to-read guide to keep by the TV for ease of reference. I would like to be able to control the sort order (e.g. by director, then by title) when generating the report and then print as a PDF or hard copy.

Many thanks in advance for any help/tips!

 

[jbeaucaire]

I would presume you would create a "template" sheet into which to enter this information one row at a time, that you would then duplicate the template for the number of rows you have.

Then you would print the entire thing to your PDF. The layout of the template is completely in your control, so that covers your "sort order".

I have a macro for taking the data from a database sheet and filling out a template sheet over and over again with the data. The original macro:

Data Into Template​

The macro fills out the template and saves each one as a separate file OR creates a separate sheet, your choice. That last part can be tweaked to do other things, this is mainly to demonstrate a simple way to fill out a form from a row-based database.

So in your case, say "no" to the "make workbooks" question. There's a sample file there to help you test it, see it in action.

 

[stefanaalten]

Jerry, thanks. Not quite what I'm trying to achieve though. I've tried the sample on your site and can see how it works. However, when it comes to printing the whole workbook, your approach results in one page per record. Even with only 700 records, as in my case, that's quite a big document (to print).

 

[jbeaucaire]

The code is a starting point, always. With the minimal information given, it is a good starting point, too.

You can customize the colored "action" section in the middle to fill out your template how you need. For instance, if your template actually is 5 "cards", then you're going to fill out five rows worth of data into your template, then you'll create the next sheet to do the next five rows.

For Rw = 2 To LastRw Step 5

You can do this. But anything more specific would require a lot more info than provided so far.

 

[stefanaalten]

Jerry thanks again but that's going way over my head I have managed to get into the Macro Visual Basic Editor and added "step 5" to try it out (using your sample) but only got one "card" per worksheet. I can see I need to do some detailed macro/visual basic programming to get your approach to work, but was hoping for an approach which avoids that ... as I don't know anything about macro/visual basic programming!

 

[jbeaucaire]

Show us some sample rows of data and your edited "action" code that is inserting the first set of data. You can do this, it's new to you, but it's not rocket science, you'll get it.

 

[stefanaalten]

For now I just dl'd the sample on your site, edited the FillOutTemplate macro by adding "step 5" to the "for" statement (as highlighted on the line of code in your earlier reply). I then return to the workbook and run the macro (alt-F8) and select "no" to the question to "create separate workbooks?" This results in two new worksheets being created in the existing workbook: Name1 and Name6. I haven't tried it with my own data yet as I just want to understand how to get this approach to work.

 

[jbeaucaire]

The code is currently using one row of data at a time. If you wanted to process 5 rows per "round", then you add more lines of code to do each group within the 5.

Currently it puts Name in B3, Address in C4, and city/state/zip D5:D7. If that template actually had 5 cards on it and each "name" field was offset by 10 rows, you could do another mini-loop like this:

Dim Lp as Long   'add this line at the top with the other Dims

    For Rw = 2 To LastRw Step 5
        tSht.Copy After:=Worksheets(Worksheets.Count)   'copy the template
        With ActiveSheet                                'fill out the form
            'edit these rows to fill out your form, add more as needed
            '.Name = dSht.Range("A" & Rw)
            For Lp = 0 To 4
                Rw = Rw + Lp
                .Range("B3").Offset(10 * Lp).Value = dSht.Range("A" & Rw).Value
                .Range("C4").Offset(10 * Lp).Value = dSht.Range("B" & Rw).Value
                .Range("D5:D7").Offset(10 * Lp).Value = dSht.Range("C" & Rw, "E" & Rw).Value
            Next Lp
        End With

Obviously this is one of many approaches, but this keeps the code short. You could just hardcode the commands to each group:

    For Rw = 2 To LastRw Step 5
        tSht.Copy After:=Worksheets(Worksheets.Count)   'copy the template
        With ActiveSheet                                'fill out the form
            'edit these rows to fill out your form, add more as needed
            '.Name = dSht.Range("A" & Rw)
            'Names
            .Range("B3").Value = dSht.Range("A" & Rw).Value
            .Range("B13").Value = dSht.Range("A" & Rw + 1).Value
            .Range("B23").Value = dSht.Range("A" & Rw + 2).Value
            .Range("B33").Value = dSht.Range("A" & Rw + 3).Value
            .Range("B43").Value = dSht.Range("A" & Rw + 4).Value

            'addresses
            .Range("C4").Value = dSht.Range("B" & Rw).Value
            .Range("C14").Value = dSht.Range("B" & Rw + 1).Value
            .Range("C24").Value = dSht.Range("B" & Rw + 2).Value
            .Range("C34").Value = dSht.Range("B" & Rw + 3).Value
            .Range("C44").Value = dSht.Range("B" & Rw + 4).Value
            
'etc....