generate report macro - complex

Dear,

I would like to generate a report.

Situation:
I have multiple excel sheets per user (see example attachment excel UAT TEST CASES USER). For a certain day I want to extract all lines from the different worktabs.

Objective macro:
- pop up ask which file to use as input to generate the report
- pop up to ask what day to use for the report (what lines to be extracted - available in column J of every tab.
- as I have multiple input excel files, the macro should be rerunnable and start on the line where the last record is populated (in order the lines are not erased and in order that all lines per input file are grouped togetehr in the one output-macro excel file)

The macro should take excel output-maco excel file to populate the records (see attachment).
- the macro should go through all tabs and check all lines with a value equal to the day that was insterted in the pop up.
- copy those lines to the output macro xls
- the first column should be populated with the scenario ID (located in field B4 of every sheet or the name of the tab
- the remaining fields are just to be extracted from the input file(s)

I hope this makes a bit sense!

If not, just come back to me for any questions!

Many manay many thanks for your help!!!

rickmeister

I have made a first try.. but not sure if I'm on the good way:

Sub report_generator()

Dim ReportSheet As Worksheet
Dim VariableDateName As String
Dim WS_COUNT As Integer
Dim I As Integer
Dim sheetje As Worksheet
Dim r As Long, endRow As Long, pasterowIndex As Long

Set ReportSheet = ActiveSheet


'file to open
fileToOpen = Application.GetOpenFilename("Select test cases report (*.xlsm), *xls", MultiSelect:=False)
VariableDateName = InputBox("Date (dd/mm/yyyy)", "Enter the test execution date to be extracted", "")
If fileToOpen <> "False" Then
Application.Workbooks.Open Filename:=fileToOpen
Else
Exit Sub
End If
Set ImportWB = ActiveWorkbook

For Each sheetje In ImportWB.Sheets

For r = 1 To endRow

If Cells(r, Columns("J").Column).Value = VariableDateName Then

Rows(r).Select
Selection.Copy

Sheets("report").Select
Rows(pasterowIndex).Select
ActiveSheet.Paste

pasterowIndex = pasterowIndex + 1

End If

Next r

Next sheetje

End Sub

working like a rock!