Present Rows from a separate sheet based on a Date Filter.

[mat0tam]

Hi all.

First post, after many months on the sideline i've decided to get involved.

I am working on summary sheet for my workbook. The workbook contains sheets (Sheets 2&3) with costs assessments for specific jobs (one per job currently).

In the job sheets the column headings are as follows:

Operation
No. of men
No. of hours
Total man hours
Hourly Cost
Total cost
Resource Discipline
Start Date
End Date
Average Daily Cost


I want to be able to enter two dates into a summary sheets (Sheet1) and it to pull through all the jobs that fall within the variables from each worksheet. Mainly to display the whole row, as from their I will easily be able to get the costs.

I foresee a lot of issues with sub headings, sub tasks etc as the job sheets are not a simple list.

Im not sure how to post up a spreadsheet (may be I cant as its my first post) but I feel seeing it would be the easiest way to explain it.

Im fairly competent in Excel but have limit VBa knowledge. I have a feeling Advanced filtering or a query macro of sorts is the way to go.

Thanks - Matthew.

 

[Jerry Sullivan]

Thanks Jerry, this is perfect. admittedly I don't understand the later part of the code;

'--union parts from each sheet into one query down to where the data is cleared and pasted.

Matt, Glad to have helped. Here's an overview of what that middle part of the code does...

The code builds a single query that is run by the statement that begins with:

oRecordset.Open Source:=sSQL,

The first part of the code steps through each project sheet and writes separate parts of the query specific to each sheet. Each part is stored as a String in the Variant array vUnionParts.

For your two-sheet example, vUnionParts (1) is assigned:
"SELECT 'WP 26' AS

, [Task Number],
[Operation],[No of men],[No of hours],
[Total man hours],[Hourly Cost], [Total Cost],
[Resource Discipline] , [Start Date], [End Date]
FROM [WP 26$B6:M32]
WHERE [Start Date]<=#7/25/2013#
AND [End Date]>=#7/14/2013# "

vUnionParts (2) is assigned:
"SELECT 'WP 27' AS [Code], [Task Number],
[Operation],[No of men],[No of hours],
[Total man hours],[Hourly Cost], [Total Cost],
[Resource Discipline] , [Start Date], [End Date]
FROM [WP 27$B6:M32]
WHERE [Start Date]<=#7/25/2013#
AND [End Date]>=#7/14/2013#"


This part of the code combines those parts into the single query that will be run.

[CODE]    Select Case lPart
        Case Is > 1
           '--more than one part. Join the parts together
            sSQL = Join$(vUnionParts, vbCr & "UNION ALL" & vbCr)
        Case 1
           '--only one part. That becomes the entire query
            sSQL = vUnionParts(1)
        Case Else
          '--no parts. to handle exceptions like 
          '   running on workbook with no projects
            Exit Sub
    End Select

(Will do a Google search now) Are you aware of any way to auto run some conditional formatting?
So for instance highlight any tasks that are the "top 5" costs for their representative sheet?

You might have already found what you want from a search. If not, it's probably best to start a new thread with more description.
Excel's built-in Conditional Formatting functionality updates automatically, so I might not be understanding what you mean by "auto run".

Makes me wish I had the time to delve more into the uses of VBa. (any recommended sites/tutorials)

There's lots of resources out there!
hiker95 maintains a list that he occasionally posts. Here is a recent update.
http://www.mrexcel.com/forum/excel-...os-visual-basic-applications.html#post3518981

 

[mat0tam]

Hi Jerry, thanks for your help so far.

---

I have since decided to develop the code to process all the information from one data page. This way I can control the format the data is inserted in.

---

I am struggling to alter the vba code to read from just this one worksheet, I believe its something to do with removing the <>Task Snapshot, and the vUnionparts script.

---

Any help understanding how to change the sheets the vba code looks in? (forgive the lines, enter doesn't seem to want to work).

---

Regards, Matt

 

[Jerry Sullivan]

Hi Matt, If there's just one dataset on that one data sheet, then much of that code can be removed.

The code was looking at all sheets Except "Task Snapshot" and taking different action depending on how many sheets were found.

I'd be glad to modify the code if you'll either post a link to an updated version, or describe where the dataset, date criteria and report destination occur.

With one just one dataset, you might consider other approaches like using Advanced Filter our AutoFilter which are somewhat easier to code.

 

[mat0tam]

Hi Jerry, Hope your well.
I agree that advanced filtering is probably the best way forward and have attempted to use this feature myself.
However I can't seem to code it effectively at the moment, the limits of my VBA knowledge are preventing me editing and amending the code produced when I record my Macros.
I am also experiencing issues setting the date criteria, as before I want to pull any task through that occurs during the 2 dates regardless of broader start/end dates.
As far as presentation format goes I think the current style is sufficient. (Also please note that for task 3.0 just the header task is sufficient).
I'm not sure if there is a clever way of pulling task 0.0 through for each workpack? I could do this without a fairly basic recorded Marco.
As before please note this isn't the exact data set I'm using but it is the same format.
Appreciate the help. (https://app.box.com/s/3yk55olis0gr5lkpc3z8)

Matt.

 

[Jerry Sullivan]

Hi Matt,

Below are some steps you can use to get you started on an AdvancedFilter approach.

First, you'll need to setup a range that will hold the criteria for the AdvancedFilter.
This example assumes you create a separate sheet named "AdvFilterCriteria" which you can then make hidden to avoid it being unintentionally modified.

Excel Workbook
	A	B
1	Start Date	End Date
2		>=41469
Sheet

Second, Paste this code into a Standard Code Module in your workbook...

Sub AdvancedFilterData()
'---Uses advanced filter to copy records meeting criteria to SnapShot sheet
    Dim rResults As Range
    
    Set rResults = Sheets("Task Snapshot").Range("C13:M29")
    
    '--clear results from previous search
    rResults.ClearContents
    
    With Sheets("Data")
        '--this allows results to exceed the number of rows in rResults 
        Application.DisplayAlerts = False
                
        .Range("A1:K" & .Cells(.Rows.Count, "B").End(xlUp).Row).AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Sheets("AdvFilterCriteria").Range("A1:B2"), _
            CopyToRange:=rResults, _
            Unique:=False
        
        Application.DisplayAlerts = True
    End With

End Sub

Third, assign this macro to your Search button
Fourth, save the workbook and test.

This will copy all tasks on your data sheet that have any dates overlapping your search date range, regardless of whether they are summary tasks. If you want to eliminate the summary tasks, additional criterion could be added to the Criteria Range to further filter the results.