Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-27-2018, 10:49 PM
paulkaye paulkaye is offline Generating / exporting a report summarizing info in a worksheet Windows 7 64bit Generating / exporting a report summarizing info in a worksheet Office 2007
Advanced Beginner
Generating / exporting a report summarizing info in a worksheet
 
Join Date: Nov 2011
Posts: 84
paulkaye is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 05-28-2018, 04:50 PM
BobBridges's Avatar
BobBridges BobBridges is offline Generating / exporting a report summarizing info in a worksheet Windows 7 64bit Generating / exporting a report summarizing info in a worksheet Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 05-29-2018, 12:44 AM
paulkaye paulkaye is offline Generating / exporting a report summarizing info in a worksheet Windows 7 64bit Generating / exporting a report summarizing info in a worksheet Office 2007
Advanced Beginner
Generating / exporting a report summarizing info in a worksheet
 
Join Date: Nov 2011
Posts: 84
paulkaye is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 05-29-2018, 01:27 PM
p45cal's Avatar
p45cal p45cal is offline Generating / exporting a report summarizing info in a worksheet Windows 10 Generating / exporting a report summarizing info in a worksheet Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
Reply With Quote
Reply

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
Generating / exporting a report summarizing info in a worksheet Not all fields are exported to the Baseline Cost Report - Visual Report Rowena Project 3 05-30-2016 04:24 PM
Generating / exporting a report summarizing info in a worksheet 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
Generating / exporting a report summarizing info in a worksheet arrows remain between task bars, but predecessor info disappears from task info Antares Project 1 12-14-2011 09:19 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:38 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft