#1
|
|||
|
|||
Generating / exporting a report summarizing info in a worksheet
I don't even know if this is possible, let alone how one would achieve it. Any insights would be gratefully received! I regularly use an Excel table spreadsheet to record information. Clients are listed in column C, and dates are listed in row 3. The data that I enter consists of (i) a value entry (time spent) in a cell itself, and (ii) a text string in a comment on that cell. I'd like to be able to generate a report that summarizes the data as follows: Client A [date] | [time] | [text string] [date] | [time] | [text string] Client B [date] | [time] | [text string] [date] | [time] | [text string] Ideally, I'd really like to be able to run the report only on selected cells, so that I could select cells within a particular period of time, and generate the report only for that information. It doesn't matter to me whether the report is another worksheet, a text file, or something else. Hoping to hear some words of wisdom! Thanks in advance. |
#2
|
||||
|
||||
Hi, Paul. I think what I'd do, if I were writing a VBA program to do this, is to create a collection—let's call it Clients—each item of which is a Client object that has properties Name (identifies the client) and Rows. Rows would be another collection, a sub-collection so to speak, each item of which has a time value and text, or if you prefer a date, a time and a text.
Your program looks at the rows you selected and loads them up, row by row, into that structure. Once it has all the data it goes through the structure client by client, and within client date by date, generating the report (in whatever format you decide). But I'm guessing you need more detail than that, or you would have done it already. Which part of the above do you not know about? Is it collections, or objects and properties, or...? Or if you don't know how to write VBA programs at all, we gotta start further back. |
#3
|
|||
|
|||
Hi Bob,
Thank you for your reply. You're correct that my knowledge is very limited. I understand conceptually your suggested approach. I think that I need to consider how useful the script will really be before I dedicate the time to building it. Many thanks again for your time. Paul |
#4
|
||||
|
||||
A guess.
Select only a single block of cells NOT including date headers row, nor clients column: Code:
Sub blah() Set myRange = Selection Set SceSht = Selection.Parent Set NewSht = Sheets.Add Set Destn = NewSht.Range("A2") For Each rw In myRange.Rows If Application.CountBlank(rw) < rw.Cells.Count Then Destn.Value = SceSht.Cells(rw.Row, 3).Value Set Destn = Destn.Offset(1) For Each cll In rw.Cells If Len(cll.Value) > 0 Then Destn.Value = SceSht.Cells(3, cll.Column).Value cll.Copy Destn.Offset(, 1) 'this also copies comments but you can remove them en masse later. Destn.Offset(, 2).Value = cll.Comment.Text Set Destn = Destn.Offset(1) End If Next cll End If Next rw End Sub |
Tags |
export, report |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Filename options when saving in Word 2013 - using report number at top of a report ? | swayzak | Word | 1 | 09-08-2017 08:54 AM |
Not all fields are exported to the Baseline Cost Report - Visual Report | Rowena | Project | 3 | 05-30-2016 04:24 PM |
Summarizing data in a workbook which the sheets contain verndor names in different orders. | pad | Excel | 3 | 10-14-2015 07:33 PM |
Automation Process of Schedule Report Output and Report Check Score Card ! | ozman86 | Word VBA | 1 | 11-19-2014 11:52 PM |
arrows remain between task bars, but predecessor info disappears from task info | Antares | Project | 1 | 12-14-2011 09:19 AM |