Generating a simple report based on some criteria

[StressLess]

Hi guys,

I have a workbook where i have some data as the following:

Equipment Equipment Description Category Failure Description Date Time

this workbook is used as a log book where whenever there's a failure i have to log that failure in this sheet.

I also have another sheet wehre i have a list of all the equipments.

What i want to do is that i want the user and from a different sheet in the workbook to select some criteria and generate a report that extract data from both sheets.

Have a look at the attached workbook.

Thanks in advance for helping me.

BR,

[StressLess]

Guys, if there's anything not clear enough please let me know

[StressLess]

guys, i'm in a desperate need for your help here. All i need is something to start. Any tips will be appreciated.

BR,

[RonMcK]

StressLess,

It appears that your log of events is on the tab "Log" and the equipment list is on the tab "FuncLoc". So, where/how do you want the user to enter selected item for the search phase of generating your report?

It appears that your report is columns C thru H of the Log file.

Do you want the report on the selected data saved as an XL sheet?

I'll work on the producing the selection while you answer my questions.

Thanks,

[RonMcK]

StressLess,

More questions, just to be sure that I'm building something that will meet your expectations.

What is the historyWS?

On/about your input WS:

What is the purpose of the Reliability Event Form (a/k/a input)?
Besides the Equipment ID, what other fields do you want used in narrowing the data selection?
If you want to be able to limit the report to a date range, where will that be entered or how will that be determined? (example, + and - "X" days)
You comment in your code that some of the fields have formulas. Is your expectation that in copying the data, the cell values will be pasted as values, stripping the formulas?
On the Group Report WS:

What is Entry Date and Entry Time, you only have a Date Entered field in the Log File? I see a Date and Time fields but those are for the incident, right?
What is Unit, your part of a larger enterprise?
Where do Failure Type and Equipment Type come from, user input?

You comment in your code that some of the fields have formulas. Is your expectation that, after copying the data, the cell values will be 'pasted as values', stripping the formulas?

On your report,

Where do I get SN (serial number?)?
Where do I get Type from?
Where does Func Location come from, the FuncLoc worksheet?
Have you considered combining your Date and Time on the log WS into a single, more easily sortable field?

These will help me help you.

Thanks,

[RonMcK]

StressLess,

Well, while I wait for your answers, I pulled the following code together to pull selected data from your 'log' worksheet (which I call logWS) and copy certain columns (cells) from 'log' to 'Group Reports' (which I call rptWS).

MD, XLD, and all the other gurus, luckers, and frustrated coders:

I need some help with the following code. My questions are noted in the following code.

Thanks, one and all, for your help for StressLess and myself.

Option Explicit

Sub GroupReport()
Dim wb As Workbook, logWS As Worksheet, rptWS As Worksheet
Dim LastCol As Long, EquipCol As String, DateCol As String, nextCol As Long
Dim LastRow As Long, nextRow As Long
Dim rng As Range, rng2 As Range
Dim TestEquip As String, TestDate As Double, n As Long

' insert whatever code you want to use to open your file
' or make sure it's already open before you execute your code

Set wb = Workbooks("EBS_All_test_new2_Test.xlsm") '
Set logWS = wb.Worksheets("log") ' e.g. Sheet1 or whatever you call it
Set rptWS = wb.Worksheets("Group Report")

With logWS
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .UsedRange.Columns.Count
Set rng2 = .Range("A2").Resize(LastRow, LastCol)
TestEquip = "76xE12"

' Sort your worksheet with the newest data either at the top or bottom, your choice.

' rng2.Sort(Key1 Order1:=xlAscending, SortMethod:=xlPinYin, DataOption1:=xlSortNormal)

' Add temp col and logic for autofilter
.Columns("L").Insert
.Range("L2").Value = "Temp"
Set rng = .Range("L3").Resize(LastRow - 1) 'a range that is just the data in col L.
rng.FormulaR1C1 = "=(UPPER(RC[-9])=""" & TestEquip & """)" ' generalize using variable names
Set rng = .Range("L2").Resize(LastRow)
rng.AutoFilter Field:=1, Criteria1:="TRUE"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible) 'this sets the range to only the visible cells

' I'm trying to copy selected columns of visible data in the above selection and paste them into the target (rptWS)
' This code is not working. The selected data is not being saved in the clipboard. When I paste, instead of my desired
' data, the last previous item copied to the clipboard is being pasted. Arrrggghhhhh.

With rptWS
logWS.Range(.Cells(3, 3), .Cells(3, 4)).Resize(LastRow).Copy = .Range(.Cells(4, 5), .Cells(4, 6)).Resize(8).PasteSpecial
logWS.Range(.Cells(3, 5), .Cells(3, 10)).Resize(LastRow).Copy = .Range(.Cells(4, 5), .Cells(4, 6)).Resize(8).PasteSpecial
End With

' If the above is not feasible, then, I'd like to use the following. However, when I cycle through the code the selected
' data on logWS is not being copied over to rptWS ("Group Report"). I don't understand why not, it works in a different
' program that I wrote with MD's assistance.

With rptWS
For n = 1 To LastRow ' VisibleRowsCount
Debug.Print n
If (logWS.Range("C", 2 + n).Visible = True) Then
.Range("E" & 3 + n) = logWS.Range("C", 2 + n) ' Equipment
.Range("F" & 3 + n) = logWS.Range("D", 2 + n) ' Equipment Description
.Range("I" & 3 + n) = logWS.Range("E", 2 + n) ' Failure Type
.Range("J" & 3 + n) = logWS.Range("F", 2 + n) ' Failure Description
.Range("K" & 3 + n) = logWS.Range("G", 2 + n) ' Date
.Range("L" & 3 + n) = logWS.Range("H", 2 + n) ' Time
.Range("M" & 3 + n) = logWS.Range("I", 2 + n) ' Note
.Range("N" & 3 + n) = logWS.Range("J", 2 + n) ' Note Description
End If
Next
End With



' When you're done, delete the column the program added and all of youir data will resume being visible

' .Columns("L").Delete

End With

End Sub

Attached is StressLess's workbook with my code added and his 'log' worksheet after the above selection was run showing the 8 selected failure records that I want to use for his report.

Thanks!!

[RonMcK]

StressLess,

A learning moment: please use consistent terms (names, titles, captions, etc) throughout a project. On logWS (log) you use the term, Category, which when you get to rptWS (Group Report) has morphed into Failure Type. The latter may be more descriptive and accurate than the former.

Thanks,

[StressLess]

Thanks you very much Ron for your help.

There will be 2 different reports.

1. Group report, where user will select some criteria and report will be generated based on these critieria and by using data from the sheets (Log, EBS).

2. Equipment Report, user will enter equipment ID, and report will be generated same as the above (using data from Log sheet and EBS sheet).

Thanks again Ron, I really appreciate your help.

[StressLess]

Answering your questions:

Input WS questions:
1. It is used to log failure event (equipment failure)
2. Date, Time, Unit, Failure Type & Equipment type
3. It should be determined by the failure date in Log sheet
4. yes, only values.

On the Group Report WS:
1. Right, entry date and time in the Log sheet are incident date and time.
2. Unit = EBS (0, 1, 2)
3. yes

On your report:
1. SN is just a counter.
2. failure type = Category (Log sheet)
3. Func Location is just an extra sheet, forget about it

Regards,

[StressLess]

Ron, Still there ?!

[RonMcK]

Ron, Still there ?!

Yes, I think that I'm back from wherever. ;-)

I'll read your replies and respond in the next several days.

Thanks,

Ron